From: | Raj Mathur <raju(at)linux-delhi(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | listas(at)guedesoft(dot)net |
Subject: | Re: Funtion to clean up strings? |
Date: | 2009-02-13 04:27:42 |
Message-ID: | 200902130957.42483.raju@linux-delhi.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Friday 13 Feb 2009, Andreas wrote:
> now ... lets get more complicated.
> Phone numbers are entered:
> 0123/4567-89 national number
> 0049/123/4567-89 the same number
> +49/123/4567-89 still the same number
>
> should come out as 0123456789 to search in this column.
> "0049" and "+49" --> 0
>
> while international numbers
> +33/123456789
> 0033/123456789
>
> should come as
> +33123456789
TEST=> create table foo(p text);
TEST=> insert into foo (select regexp_split_to_table('0123/4567-89
0049/123/4567-89 +49/123/4567-89 +33/123456789 0033/123456789',' '));
TEST=> select * from foo;
p
------------------
0123/4567-89
0049/123/4567-89
+49/123/4567-89
+33/123456789
0033/123456789
(5 rows)
TEST=> select
(case
when p ~ E'^(\\+|00)49'
then '0'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+|00)49(.*)', E'\\1')
when p ~ E'^(\\+|00)'
then '+'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+||00)(.*)', E'\\1')
else
regexp_replace(p, E'[^0-9]', '', 'g')
end)
from foo;
regexp_replace
----------------
0123456789
0123456789
0123456789
+33123456789
+33123456789
(5 rows)
That do what you want? (Apologies for the wrapped lines.)
Regards,
-- Raju
--
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
From | Date | Subject | |
---|---|---|---|
Next Message | Tovo Rabemanantsoa | 2009-02-13 13:18:24 | Re: Grass Root Protectionism |
Previous Message | Andreas | 2009-02-13 00:15:54 | Re: Funtion to clean up strings? |