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

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

Share your answers..

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

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]

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