Postgres Pro
Downloads
Home   >   mailing lists

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

How to create function returning numeric from string containing percent character

From
"Andrus"
Date:
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.

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

From
"David G. Johnston"
Date:
On Wed, Jul 22, 2020 at 3:50 AM 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

    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.

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

From
Ben Madin
Date:

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.





--

Ausvet Logo

Dr Ben Madin
 
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Skype: benmadin
Address: 
5 Shuffrey Street
Fremantle, WA 6160
Australia