hi david
David Gaudine schrieb:
> Is there a function that will convert a numeric string to a number,
> returning zero if the string is empty?
>
> Here is the problem I'm trying to solve; I have a column of type
> VARCHAR(4) that usually, but not always, contains a number between 0 and
> 100, possibly with a decimal point. I have to do two things;
if the value is not a number, what is it then? Can it be anything, or
do you have a set of values? if so, couldn't you replace all
non-numeric values by a numeric code (maybe in the range between 100 and
200) with an additional table holding the alphanumeric representation of
the codes. would be nice to have one data type in one column ;-)
christoph
>
> - Query according to the range of the number, i.e.
> where value >0 and value < 5
>
> Because it's varchar, a number like 17 is between 0 and 5. For this
> particular example I can work around the problem by using
> where value ~ '^ *0*[0-9](\.[0-9]*)* *$' and value > 0 and value < 5
> to allow only one digit before the decimal. (I allowed for
> leading/trailing spaces and leading zeros just in case.)
>
> - Sort by that number, i.e.
> order by value
>
> That, I can't work around. If I use
> order by to_number ( value, '000.99' )
> thus adding leading zeros to the test, the order is correct, but the
> query fails if there are any empty values. So I need to either find a
> function that converts a numeric string to a float, find a function like
> to_number but that doesn't choke on empty values, or write the query
> such that my regular expression is combined with to_number. The c/php
> trinary operator would be good here:
> order by ( value ~ '^ *0*[0-9](\.[0-9]*)* *$' ) ?
> to_number(value,'000.999) else "";
> or something like that, to call to_number only if the string contains a
> number, but I don't know the postgresql approach.
>
> If I have to write my own function, any pointers to tutorials?
>
> David
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
