Thanks Adrian ~ See comments at end. > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Adrian Klaver > Sent: Friday, June 06, 2008 11:49 AM > To: pgsql-general@xxxxxxxxxxxxxx > Cc: Ken Winter > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields > > On Friday 06 June 2008 8:25 am, Ken Winter wrote: > > Thanks, Joshua ~ > > > > What you suggest is basically what I'm trying to do. Where I'm stuck is > in > > finding a construct (a CAST or whatever) to turn the existing "money" > > column data (directly or indirectly) into numeric. I've tried to > convert a > > column named "amount" in the following ways, with the following results: > > > > CAST(amount AS numeric) -> "cannot cast type money to numeric" > > CAST(amount AS numeric(10,2)) -> "cannot cast type money to numeric" > > CAST(amount AS decimal) -> "cannot cast type money to numeric" > > CAST(amount AS text) -> "cannot cast type money to text" > > CAST(amount AS varchar) -> "cannot cast type money to character varying" > > to_char(money) -> "function to_char(money) does not exist" > > I don't know if this helps. ... > **There is no simple way of doing the reverse in a locale-independent > manner, > namely casting a money value to a numeric type. If you know the currency > symbol and thousands separator you can use regexp_replace(): > > SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric; > I'm indeed trying to get rid of US$ notation. Alas, here's what I get from trying the regexp pattern on my "amount" column (type=money): regexp_replace(amount::money::text, '[$,]', '', 'g')::numeric -> "cannot cast type money to text" regexp_replace(amount::text, '[$,]', '', 'g')::numeric -> "cannot cast type money to text" And if remove the cast to text, I get: regexp_replace(amount::money, '[$,]', '', 'g')::numeric -> " function regexp_replace(money, "unknown", "unknown", "unknown") does not exist" ~ Ken
