Re: Funtion to clean up strings?

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-890049/123/4567-89+49/123/4567-89+33/1234567890033/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
----------------012345678901234567890123456789+33123456789+33123456789
(5 rows)

That do what you want?  (Apologies for the wrapped lines.)

Regards,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves