Re: Casting Varchar to Numeric

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

translate (litre_amount, '.', '')::integer / case strpos(litre_amount, '.')
when 0 then 1 else (10^(char_length (litre_amount) - strpos(litre_amount,
'.'))) end

works for positive/negative and with/without decinal point.

You could equally do this straight from varchar I would imagine with:

translate (litre_amount, '.', '')::text::integer / case strpos(litre_amount,
'.')  when 0 then 1 else (10^(char_length (litre_amount) -
strpos(litre_amount, '.'))) end

Would be pretty could is some could implement this in PostgreSQL natively
(and more quickly!). Why not let to_number and to_char work as in the Oracle
way and generically cast numerical fields back and forth into strings. This
kind of thing makes people give up at the first hurdle when they start
looking at products.

Cheers

Andy Marden