Re: Testing castability of text to numeric

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(at)matrix(dot)gatewaynet(dot)com
> mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster