Re: How to create function returning numeric from string containing percent character

On Wed, Jul 22, 2020 at 3:50 AM Andrus <kobruleht2(at)hot(dot)ee> wrote:

> 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
>
> SELECT coalesce(nullif('0'||substring(Translate($1,',','.'),
> '^-?[0-9]+\.?[0-9]*$'),''),'0')::numeric;
> select val('1,2%')
> How to force it to return 1.2 ?
>
> It should work starting from Postgres 9.0

Removing the $ from your regexp pattern should work for the 4 provided
examples.

You cannot remove stuff from the end of a string if you require that the
end of said string match what you want to return.

David J.