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

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)

>
> Thank You!
>
> David J.
>


--
Adrian Klaver
adrian.klaver@aklaver.com