Re: Suggested (or existing) way to parse currency into numeric?

On Fri, Jun 12, 2015 at 12:57 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 06/12/2015 09:46 AM, David G. Johnston wrote:
>
>> Version 9.3
>> CREATE TABLE t ( field numeric NULL );
>> SELECT * FROM json_populate_record(null::t, '{ "field": "$18,665"
>> }'::json);
>> Error: invalid input syntax for type numeric: "$18,665"
>>
>> I can accept the type of field being something like "numeric_cleaned"
>> which has a custom input function that would strip away the symbols and
>> commas (not too concerned about locale at the moment...) and am
>> pondering writing my own custom type with supporting SQL function to
>> accomplish that but I'm hoping the community can point me to something
>> already existing.
>>
>> I really want to avoid going through a staging table. I'm more inclined
>> to brute force the source JSON using "jq" (or sed) before I would go
>> that route.
>>
>> Thoughts, suggestions, comments?
>>
>
> test=> CREATE TABLE t ( field money NULL );
> CREATE TABLE
> test=> SELECT * FROM json_populate_record(null::t, '{ "field": "$18,665"
> }'::json);
> field
> ------------
> $18,665.00
> (1 row)
>

​I wrote that type off as something I would never code into my own schema
so basically forgot about its usability in other situations.

Thank you for the reminder.

David J.​