Re: Casting Varchar to Numeric

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 :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com