From: Dean Gibson (DB Administrator) <dba-sql@ultimeth.net>
To: pgsql-sql@postgresql.org
Subject: Re: Testing castability of text to numeric
Date: Mon, 19 May 2003 17:28:05 -0700
Message-ID: <5.1.0.14.2.20030519171925.00ad4db0@imaps.ultimeth.net> (raw)
In-Reply-To: <Pine.LNX.4.44.0305161248550.10647-100000@matrix.gatewaynet .com>
References: <3EC4B2BA.B872FCDF@rodos.fzk.de>
Try something like this:
numeric_column = CASE
WHEN trim( text_column, '0123456789' ) = '' THEN
text_column::NUMERIC
ELSE
NULL
END
If your text_column also has blanks (leading or trailing), you may want to adapt the above slightly, but I think you get the idea.
I don't know how to do arbitrarily formatted dates.
-- Dean
Achilleus Mantzios wrote on 2003-05-16 07:57:
>On Fri, 16 May 2003, Christoph Haller wrote:
>
>> >
>> > I am trying to create a function that will test if a given text value
>> > may safely be cast to numeric, returning the numeric cast or null if
>> > impossible.
>
>I think that progress is being made on
>having java as a serverside language
>(i.e. plpgjava).
>Stored procedures in java would be really handy in order
>to do staff like that, java is known for its
>"exceptional" exception handling!
>
>What language do you use to bould your applications??
>
>> >
>> > Is there a way to "catch" the "ERROR: Bad numeric input format"
>> error?
>> >
>> > I ask that rather than about what the numeric format is for
>> regex-style
>> > testing because if this works I may want to use a similar method to
>> > check if a given text value casts safely to other types, like date.
>> >
>> I don't know of any way to catch errors.
>> And I can't see any way how to test castability via regex. Can you?
>> The only way I can think of at the moment is writing a C function.
>>
>> Regards, Christoph
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
>--
>==================================================================
>Achilleus Mantzios
>S/W Engineer
>IT dept
>Dynacom Tankers Mngmt
>Nikis 4, Glyfada
>Athens 16610
>Greece
>tel: +30-210-8981112
>fax: +30-210-8981877
>email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dba-sql@ultimeth.net
Subject: Re: Testing castability of text to numeric
In-Reply-To: <5.1.0.14.2.20030519171925.00ad4db0@imaps.ultimeth.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox