How to create function returning value up to first non-digit/decimal charcater

Question

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
val('')  shound return 0

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

Update

Gordon Fiddle for 9.5 support returns null for not numeric string. I solved it using wrapper:

CREATE OR REPLACE FUNCTION public.VALwithNull(value text)
  RETURNS numeric AS
$BODY$
SELECT    replace( (regexp_matches($1, '^-?[0-9]+,*[0-9]*'))[1], ',', '.') ::numeric;
$BODY$ language sql immutable;

CREATE OR REPLACE FUNCTION public.VAL(value text)
  RETURNS numeric AS
$BODY$
SELECT coalesce( VALwithNull($1) ,0) ;
$BODY$ language sql immutable;

select  val('') , val('test'), val('-77,7%')

Is this best way to support 9.0+ ?

Answer 1

I think this regular expression does what you want:

replace( (regexp_match($1, '^-?[0-9]+,*[0-9]*') )[1], ',', '.')

Here is a db<>fiddle.

You can also use regexp_matches(). It does the same thing in this context.