From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | How to create function returning numeric from string containing percent character |
Date: | 2020-07-22 10:49:53 |
Message-ID: | 7B47085A6D564BA6BCEB1509909205BF@dell2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
val function should return numeric value from string up to first non-digit character, considering first decimal point also:
val('1,2TEST') should return 1.2
val('1,2,3') should return 1.2
val('-1,2,3') should return -1.2
I tried
CREATE OR REPLACE FUNCTION public.VAL(value text)
RETURNS numeric AS
$BODY$
SELECT coalesce(nullif('0'||substring(Translate($1,',','.'), '^-?[0-9]+\.?[0-9]*$'),''),'0')::numeric;
$BODY$ language sql immutable;
but if string contains % character,
select val('1,2%')
returns 0.
How to force it to return 1.2 ?
It should work starting from Postgres 9.0
Posted also in
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2020-07-22 11:10:44 | Re: Logical replication from 11.x to 12.x and "unique key violations" |
Previous Message | Thomas Kellerer | 2020-07-22 05:36:15 | Re: Logical replication from 11.x to 12.x and "unique key violations" |