Split Comma separated string insert into Separate column .

• $agar

  SSC-Addicted

  Points: 427

  More actions

  May 7, 2013 at 3:29 am

  #277230

  DECLARE @strCSVString VARCHAR(4000)

  SET @strCSVString = 'Vinayak,20-07-2013,100,Sagar,23-10-2012,334,Leena,03-08-2014,93,Meera,20-07-2013,100,Ajad,20-07-2013,100,Ajay,23-10-2012,334,Vijay,03-08-2014,93'

  DROP TABLE #FinalResult

  DROP TABLE #Result

  CREATE TABLE #Result(ColumnValue NVARCHAR(100),cnt INT,rno INT)

  CREATE TABLE #FinalResult(Name NVARCHAR(100),Date NVARCHAR(100),Num NVARCHAR(100))

  DECLARE @C INT

  DECLARE @STR VARCHAR(20)

  DECLARE @idx INT

  DECLARE @cnt INT

  DECLARE @rno INT

  DECLARE @count INT

  DECLARE @name VARCHAR(20)

  DECLARE @date VARCHAR(20)

  DECLARE @num VARCHAR(20)

  SET @C =1

  SET @cnt = 1

  SET @rno = 1

  IF ISNULL(@strCSVString,'') <> ''

  BEGIN

  SET @idx = CHARINDEX(',', @strCSVString)

  WHILE @idx > 0

  BEGIN

  SET @STR = SUBSTRING(@strCSVString, 1, @idx - 1)

  SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx)

  INSERT INTO #Result values (@str,@cnt,@rno)

  SET @idx = CHARINDEX(',' , @strCSVString)

  SET @cnt =@cnt+1

  IF (@cnt>3)

  BEGIN

  SET @cnt =1

  SET @rno = @rno +1

  END

  END

  PRINT 'I am here'

  SET @STR = @strCSVString

  INSERT INTO #Result values (@str,@cnt,@rno)

  -- SELECT * FROM #Result

  END

  SELECT @count=COUNT(*)/3 FROM #Result

  WHILE(@count<>0)

  BEGIN

  SELECT @name=ColumnValue from #Result WHERE rno=@c and cnt=1

  SELECT @date=ColumnValue from #Result WHERE rno=@c and cnt=2

  SELECT @num=ColumnValue from #Result WHERE rno=@c and cnt=3

  SET @count=@count-1

  SET @C=@c+1

  INSERT INTO #FinalResult (Name,Date,Num)

  SELECT @name,@date,@num

  END

  SELECT * FROM #FinalResult

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  May 7, 2013 at 3:52 am

  #1612903

  There's no question here 😉

  However, I note that you're attempting an inefficient T-SQL splitter. Have a read through this article (http://www.sqlservercentral.com/articles/Tally+Table/72993/) by Jeff Moden[/url], for a better one.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---