Skip site navigation (1) Skip section navigation (2)

Function to convert numeric string to number in version 8.0

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 (view raw or flat)
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


Responses

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group