[SQL] Funtion to clean up strings?

2009-02-12 Thread Andreas 

Hi,

I need a function that removes characters in strings that aren't in a 
given set of chars.

e.g
Input:12-34/ 56(8)
I want just numbers so Output should in this case be:  1234568

Is there a solution?


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Funtion to clean up strings?

2009-02-12 Thread Dickson S. Guedes 
2009/2/12 Andreas :
> Hi,
>
> I need a function that removes characters in strings that aren't in a given
> set of chars.
> e.g
> Input:12-34/ 56(8)
> I want just numbers so Output should in this case be:  1234568
>
> Is there a solution?

One is:

SELECT regexp_replace('12-34/ 56(8)',E'[^0-9]','','g');

-- 
Dickson S. Guedes
-
mail/xmpp: [email protected] - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Funtion to clean up strings?

2009-02-12 Thread Andreas 

Wow, that was fast
THANKS   :)

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

I fear the hard part is that international codes have 1-3 digits   :(


Regards
Andreas


ivan Stoykov wrote:

regexp_replace('120323423 23(fdf_)fd','[^[:digit:]]','','gi')

Andreas wrote:
  

Hi,

I need a function that removes characters in strings that aren't in a
given set of chars.
e.g
Input:12-34/ 56(8)
I want just numbers so Output should in this case be:  1234568

Is there a solution?


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Funtion to clean up strings?

2009-02-12 Thread Raj Mathur 
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 [email protected]  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

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql