August 8, 2014

SQL Server - Split the string and insert into table

This script using common table expression below will help to split the given string and insert into the data table.

DECLARE @TempTable TABLE(Name varchar(50))

DECLARE @String VARCHAR(255) = 'Kale,Gore,Nepte,Chepte'

;WITH CTE AS
(
    SELECT LEFT(@String, CHARINDEX(',', @String) -1) AS Name, RIGHT(@String, LEN(@String) - CHARINDEX(',', @String)) AS RemainingString
    UNION ALL
    SELECT LEFT(RemainingString, CHARINDEX(',', RemainingString) -1) AS Name, RIGHT(RemainingString, LEN(RemainingString) - CHARINDEX(',', RemainingString)) AS RemainingString
    FROM CTE
    WHERE CHARINDEX(',', RemainingString)>0
    UNION ALL
    SELECT RemainingString AS Name, NULL AS Remainder
    FROM CTE
    WHERE CHARINDEX(',', RemainingString)=0
)

Insert into @TempTable (Name) SELECT Name FROM CTE

SELECT * FROM @TempTable


No comments: