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
https://stackoverflow.com/questions/63032072/how-to-create-function-returning-value-up-to-first-non-digit-decimal-charcater#63032126
Andrus.
