From: | Ben Madin <ben(at)ausvet(dot)com(dot)au> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to create function returning numeric from string containing percent character |
Date: | 2020-07-29 15:16:34 |
Message-ID: | CA+weYr1=d3OvRr0k=DG7nrjJJgv2_Fe2eq8pom2VbPMfHCyPiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
> 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.
>
>
>
>
--
[image: Ausvet Logo] <https://www.ausvet.com.au/>
Dr Ben Madin
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben(at)ausvet(dot)com(dot)au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier Leprêtre | 2020-07-29 15:44:58 | Track pgsql steps |
Previous Message | Tom Lane | 2020-07-29 15:05:16 | Re: Out of memory with "create extension postgis" |