1

Source column

Id   | email
_______________________
01   | aemail1, wemail2
02   | bemail1 : xemail2
03   |  cemail1  yemail2
04   | demail1&zemail2

Target column

Id | primary_email| secondary_email
01 | aemail1      | wemail2
02 | bemail1      | xemail2
03 | cemail1      | yemail2
04 | demail1      | zemail2

In some cases where <space> is also a delimiter can we do using SQL if not pl/SQL please help me how to solve this

bhargav reddy
  • 33
  • 1
  • 3
  • 12
  • 3
    Seems like you've learned the lesson now - never store data in the same column as separated items. – jarlh Nov 27 '15 at 13:14
  • The best way would be to normalize the table. – Anton Afanasjew Nov 27 '15 at 13:15
  • Here you can find some clues `http://stackoverflow.com/questions/23649813/split-comma-seperated-values-of-a-column-in-row-through-oracle-sql-query` – Moumit Nov 27 '15 at 13:15
  • Here am using multiple delimiters not a single delimiter here & is also included, if I use & it pop-up for run time data input. So help me how to do in SQL and don't want to change anything from source – bhargav reddy Nov 27 '15 at 13:41
  • Can you list down all the possible delimiters your `email` column may contain? Will there be any special characters in the `email` column? – mahi_0707 Nov 28 '15 at 21:24

1 Answers1

0

You can use regular expressions to split the emails into two. This example splits on one or more consecutive delimiters , or space or &

select 
  ID, 
  regexp_replace(EMAIL, '^(.*)[,\s&]+.*', '\1') AS PRIMARY_EMAIL,
  regexp_replace(EMAIL, '.*[,\s&]+(.*)', '\1') AS SECONDARY_EMAIL 
from TABLE_NAME
ramana_k
  • 1,933
  • 2
  • 10
  • 14