On Wed, Jul 22, 2020 at 3:50 AM Andrus <kobrule...@hot.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.