CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008split
Kirk1993
Kirk1993

Reputation: 31

Custom split email column names into multiple columns in SQL

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

Answers (1)

John Cappelletti
John Cappelletti

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

Related Questions