Sunday, 4 September 2016

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)


WHAT OTHERS ARE READING/RELATED TO THIS:

6 comments:

  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

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

    ReplyDelete
  3. 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]

    ReplyDelete
  4. 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

    ReplyDelete
    Replies
    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.
      Thanx for your comment.

      Delete