Re: Casting Varchar to Numeric

On Wed, Dec 05, 2001 at 05:09:48PM -0500, Jan Wieck wrote:
> Martijn van Oosterhout wrote:
> > On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote:
> > > Well, I've finally sorted it out and can now convert text/varchar fields
> > > into numeric I cannot BELIEVE I've had to resort to such things.
> > >
> > > I've changed all my varchar fields to text and then applied this to them
> > > (column name is 'litre_amount'):
> >
> > <snip ugly conversion method>
> >
> > Does this work?
> >
> > select litre_amount::float::numeric;
>
> Maybe it works, but with the step through float you loose
> precision. In the old days where the type input/output
> functions wheren't protected, one was able to use
>
> select numeric_in(textout(litre_amount)) from ...
>
> Well, some thought it'd not be such a good idea to let end
> users muck around with C string pointers, and IIRC I was one
> of them.
>
> But there are still the internal casting capabilities of
> PL/pgSQL. What about
>
> CREATE FUNCTION to_numeric ( text ) RETURNS numeric AS '
> BEGIN
> RETURN $1;
> END;' LANGUAGE 'plpgsql';
>
> Maybe this function is far too overcomplicated and someone
> might enhance the algorithm :-)

We already have to_number() that cast from string to numeric...

test=# SELECT to_number('1234.5678', '9999999999999999.999999999999999999');
to_number
-----------
1234.5678
(1 row)

... small problem is that you must set expectant format of string.

http://www.postgresql.org/idocs/index.php?functions-formatting.html

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz