# How to split a comma-separated value to columns| SQL Server Interview Question

Q. How to split a comma-separated value to columns. Ex('First,Second,Third')
Output should be as below

Ans:
First Solution (it is very complex)
DECLARE @S NVARCHAR(100) = 'First,Second,Third'
SELECT SUBSTRING(@S,0,CHARINDEX(',',@S,0)) Column1,
SUBSTRING(@S,(CHARINDEX(',',@S,0))+1,(CHARINDEX(',',@S,(CHARINDEX(',',@S,0)+1)))-(CHARINDEX(',',@S,0))-1) Column2,

SUBSTRING(@S,(CHARINDEX(',',@S,(CHARINDEX(',',@S,0)+1)))+1,LEN(@S)) Column3

Second Solution (Very good)

1. DECLARE @S NVARCHAR(100) = 'First,Second,Third'
SELECT SUBSTRING(@S,0,CHARINDEX(',',@S,0)) Column1,
SUBSTRING(@S,(CHARINDEX(',',@S,0))+1,(CHARINDEX(',',@S,(CHARINDEX(',',@S,0)+1)))-(CHARINDEX(',',@S,0))-1) Column2,
SUBSTRING(@S,(CHARINDEX(',',@S,(CHARINDEX(',',@S,0)+1)))+1,LEN(@S)) Column3

3. please send me sql interview question in my id prakashkw2@gmail.com

4. Second solution script
DECLARE @names NVARCHAR(100) = 'First,Second,Third'
DECLARE @X XML
SELECT @X = CONVERT(XML,''+REPLACE(@names,',','')+'')

SELECT @X.value('/Names[1]/name[1]','varchar(100)') [Column1],
@X.value('/Names[1]/name[2]','varchar(100)') [Column2],
@X.value('/Names[1]/name[3]','varchar(100)') [Column3]

5. SELECT SUBSTR('First,Second,Third',1,5)COLMN1,
SUBSTR('First,Second,Third',INSTR('First,Second,Third','S'),6)COLUMN2
,SUBSTR('First,Second,Third',INSTR('First,Second,Third','T'))COLUMN3
FROM DUAL;
----THIS QUERY ALSO GIVES OUTPUT BT I WANT TO KNW THAT IT IS CORRECT WAY OR NOT CAN ANYWAY HELP MI..
THANX

1. Hi Pallavi, You have oracle query but here I have written SQL Server query, Both are correct way but in SQL server XML way(last one) is good way to do this because it is more clear and don't be more complex if we have more column. but first way become more complex if we have more column.