Reputation: 31
I have an email column with 3-4 emails in each row which i want to split into one email per column:
Current columns looks like this:
Email_column
1. [email protected] [email protected] [email protected]
Expected output should be:
Email_1 Email_2 Email_3
1. [email protected] [email protected] [email protected]
Upvotes: 1
Views: 433
Reputation: 82020
With a CROSS APPLY and a little XML
Example
Declare @YourTable table (ID int,Email_column varchar(max))
Insert Into @YourTable values
(1,'[email protected] [email protected] [email protected]')
Select A.ID
,B.*
From @YourTable A
Cross Apply (
Select Pos1 = n.value('/x[1]','varchar(max)')
,Pos2 = n.value('/x[2]','varchar(max)')
,Pos3 = n.value('/x[3]','varchar(max)')
,Pos4 = n.value('/x[4]','varchar(max)')
From (Select Cast('<x>' + replace(A.Email_column,' ','</x><x>')+'</x>' as xml) as n) X
) B
Returns
ID Pos1 Pos2 Pos3 Pos4
1 [email protected] [email protected] [email protected] NULL
Upvotes: 3