Split a string with 2 delimiters in SQL Server

Question

I have some wrong data in my SQL Server database.

It's look like this:

abc.qwerty@yahoo.com|fubar@cc.uk|helloworld@gmail.com

or

abc.qwerty@yahoo.com;fubar@cc.uk

How can I split them to separate email if the delimiter can be anyone of these '|', ';', ','.

After spliting, the result will be add to 3 column in the same table:

Email1: abc.qwerty@yahoo.com, Email2: fubar@cc.uk, Email3: helloworld@gmail.com

Answer 1

 DECLARE @STR NVARCHAR(MAX)='abc.es@yahoo.com|shudo@cc.uk|maria@gmail.com,      abc.es@yahoo.com;shudo@cc.uk'

-- Converts values to rows
SELECT Split.a.value('.', 'VARCHAR(100)') 'Ids' 
FROM  
(
     -- Use 3 REPLACE for  '|', ';', ','
     SELECT CAST ('<M>' + REPLACE(REPLACE(REPLACE(@STR, '|', '</M><M>'),',','</M><M>'),';','</M><M>') + '</M>' AS XML) AS Data      

) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

• See the working FIDDLE here

UPDATE

If you want Comma Separated Value as final string you can do the below

DECLARE @STR NVARCHAR(MAX)='abc.qwerty@yahoo.com|fubar@cc.uk|helloworld@gmail.com,abc.qwerty@yahoo.com;fubar@cc.uk '
DECLARE @Final VARCHAR(MAX)='';


-- Converts values to rows
;WITH CTE AS
(
    SELECT DISTINCT 
    Split.a.value('.', 'VARCHAR(100)') 'Ids' 
    FROM  
    (
         -- Use 3 REPLACE for  '|', ';', ','
         SELECT CAST ('<M>' + REPLACE(REPLACE(REPLACE(@STR, '|', '</M><M>'),',','</M><M>'),';','</M><M>') + '</M>' AS XML) AS Data      

    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
-- Convert to Comma Seperated Values
SELECT @Final += 
Isnull('Email' + CAST(ROW_NUMBER() OVER(ORDER BY IDS)AS VARCHAR(10))  + ': ' + Ids, '') + ', ' 
FROM CTE

SELECT LEFT(@Final,len(@Final)-1)

PRINT @Final

UPDATE 2 : Split delimiter separated values to rows and convert to columns dynamically

I have written logic inside query

DECLARE @STR NVARCHAR(MAX)='abc.es@yahoo.com|shudo@cc.uk|maria@gmail.com,abc.es@yahoo.com;shudo@cc.uk'

;WITH CTE AS
(
        -- Converts values to rows
    SELECT DISTINCT Split.a.value('.', 'VARCHAR(100)') 'Ids'
    FROM  
    (
         -- Use 3 REPLACE for  '|', ';', ','
         SELECT CAST ('<M>' + REPLACE(REPLACE(REPLACE(@STR, '|', '</M><M>'),',','</M><M>'),';','</M><M>') + '</M>' AS XML) AS Data      

    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
-- Create a column to order Emails like Email1,Email2.... and insert to a temp table
-- It can be done without temp table, but its for sake of readability
SELECT 'Email' + CAST(ROW_NUMBER() OVER(ORDER BY Ids)AS VARCHAR(10)) EMails,Ids
INTO #TEMP
FROM CTE

-- Get columns to pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + EMails + ']','[' + EMails + ']')
               FROM    (SELECT DISTINCT EMails FROM #TEMP) PV  
               ORDER BY EMails

-- Pivot the result(convert to columns from rows)
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT EMails,Ids 
                 FROM #TEMP
             ) x
             PIVOT 
             (
                 MIN(Ids)
                 FOR EMails IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

• SQL FIDDLE

Now if you want to insert emails into your table you have created, you can use the below code

DECLARE @query NVARCHAR(MAX)
SET @query = 'INSERT INTO YOURTABLE(' + @cols + ')
             SELECT * FROM 
             (
                 SELECT EMails,Ids 
                 FROM #TEMP
             ) x
             PIVOT 
             (
                 MIN(Ids)
                 FOR EMails IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Answer 2

Step one I would clean your data. SQL Server has a Replace function you can use.

REPLACE ( string_expression , string_pattern , string_replacement )

Example you can run in SQL Server Management Studio:

select Replace(Replace('ab|de,ghi;de', ',', '|'), ';', '|')

Would first replace the commas, and then the semicolons. Resulting in ab|de|ghi|de

Alternatively, you could just split up the data multiple times by running a cursor on the resulting split (with whatever mechanism you are using to split.