CODEWITHSUNDEEP
plsqloracle10g
user2567381
user2567381

Reputation: 7

Formatting email id strings using regexp

I have a string with email addresses say for example

,[email protected], [email protected], [email protected],

[email protected],, [email protected], [email protected],[email protected],

I want the out put to be 

[email protected],[email protected],[email protected]
[email protected],[email protected],[email protected],[email protected]

I tried many options but couldn't get the desired results ...sometimes with double commas, comma at the start and end of the string.

Can this be achieved in a single sql statement using regexp?

I am using Oracle 10g.

Upvotes: 0

Views: 55

Answers (2)

saurabh raj
saurabh raj

Reputation: 21

--with Regular Expression we can do like this.

WITH testdata AS
  (SELECT ',[email protected], [email protected], [email protected],' AS e FROM dual
  UNION ALL
  SELECT '[email protected],, [email protected], [email protected],[email protected],'
  FROM dual
  )
SELECT trim(','
FROM regexp_replace((regexp_replace(e,',|[[:space:]]',NULL)),'gmail.com','gmail.com,'))
FROM testdata

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

with testdata as
  (select ',[email protected], [email protected], [email protected],' as e from dual
   union all
   select '[email protected],, [email protected], [email protected],[email protected],' from dual)
select TRIM(',' FROM REPLACE(REPLACE(e,' '),',,',','))
from testdata;

[email protected],[email protected],[email protected]
[email protected],[email protected],[email protected],[email protected]

Upvotes: 1

Related Questions