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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

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