Thread: Function to convert numeric string to number in version 8.0

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;

- 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

On Thu, Mar 09, 2006 at 16:13:47 -0500,
  David Gaudine <davidg@alcor.concordia.ca> wrote:
> Is there a function that will convert a numeric string to a number,
> returning zero if the string is empty?

You can use to_number to convert a string to a number. You can combine
that with a case statement to only convert strings that look like a number
and return zero for strings that don't look like a number.

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
>
>