Skip site navigation (1)
Skip section navigation (2)
## Function to convert numeric string to number in version 8.0

### Responses

### pgsql-novice by date

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

- Re: Function to convert numeric string to number in version 8.0 at 2006-03-09 22:35:04 from Bruno Wolff III
- Re: Function to convert numeric string to number in version at 2006-03-10 06:51:12 from Christoph Della Valle

Next: From:Bruno Wolff IIIDate:2006-03-09 22:35:04Subject: Re: Function to convert numeric string to number in version 8.0Previous: From: Christopher A. GoodfellowDate: 2006-03-09 19:37:54Subject: Re: Invalid Page Header