Home > mailing lists

Re: How to create function returning numeric from string containing percent character - Mailing list pgsql-general

From	Ben Madin
Subject	Re: How to create function returning numeric from string containing percent character
Date	July 29, 2020 18:16:34
Msg-id	CA+weYr1=d3OvRr0k=DG7nrjJJgv2_Fe2eq8pom2VbPMfHCyPiw@mail.gmail.com Whole thread Raw
In response to	How to create function returning numeric from string containing percent character ("Andrus" <kobruleht2@hot.ee>)
List	pgsql-general

Tree view

I suspect it will depend on your localisation whether you need to account for different decimal separators, but just in case:

SELECT replace(substring('-1,2%' from '^-?\d*[.,]?\d*'), ',', '.')::numeric;

On Wed, 22 Jul 2020 at 18:50, Andrus <kobruleht2@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

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.