From: | David Gaudine <davidg(at)alcor(dot)concordia(dot)ca> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Function to convert numeric string to number in version 8.0 |
Date: | 2006-03-09 21:13:47 |
Message-ID: | 44109A8B.5070709@alcor.concordia.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2006-03-09 22:35:04 | Re: Function to convert numeric string to number in version 8.0 |
Previous Message | Christopher A. Goodfellow | 2006-03-09 19:37:54 | Re: Invalid Page Header |