CommaSeperatedStringToTable

Sravan661
Posted by Sravan661 under Sql Server category on | Points: 40 | Views : 706
Hi,
To convert a commaseperated String to Table use the following query.
For better coding in projects create this as a function and use if required.

DECLARE @InputString VARCHAR(MAX) ='DotNet,Java,SqlServer'
DECLARE @TOTAL INT;
DECLARE @MAINSTRING VARCHAR(1000)
DECLARE @SUBSTRING VARCHAR(100)
DECLARE @REMAININGSTRING VARCHAR(1000)
DECLARE @INTINDEX INT
DECLARE @I INT
DECLARE @TEMP_TAB TABLE
(
ID INT IDENTITY(1,1),
VALUE VARCHAR(100)
)

SET @TOTAL=(LEN(@InputString) - LEN(REPLACE(@InputString, ',', '')))
SET @MAINSTRING=@InputString
SET @I=0;

WHILE @I <= @TOTAL
BEGIN
IF @I=0
BEGIN
SET @INTINDEX=CHARINDEX(',',@MAINSTRING,1)
SET @SUBSTRING=SUBSTRING(@MAINSTRING,1,@INTINDEX-1)
SET @REMAININGSTRING=SUBSTRING(@MAINSTRING,@INTINDEX+1,1000)
INSERT INTO @TEMP_TAB
SELECT @SUBSTRING

END
ELSE IF @I=@TOTAL
BEGIN
INSERT INTO @TEMP_TAB
SELECT @REMAININGSTRING
END
ELSE
BEGIN
SET @INTINDEX=CHARINDEX(',',@REMAININGSTRING,1)
SET @SUBSTRING=SUBSTRING(@REMAININGSTRING,1,@INTINDEX-1)
SET @REMAININGSTRING=SUBSTRING(@REMAININGSTRING,@INTINDEX+1,1000)
INSERT INTO @TEMP_TAB
SELECT @SUBSTRING
END
SET @I=@I+1;
END

select * from @temp_Tab

Hope this helps you
happy Coding . . .

Comments or Responses

Posted by: kgovindarao523-21772 on: 11/20/2014 Level:Bronze | Status: [Member] [MVP] | Points: 10
Hi,
This is good.some posts are not working properly some scenarios, and Even they code is going to continues loop.
http://www.dotnetfunda.com/codes/show/4250/print-comma-seperated-values-in-sql-server
http://www.dotnetfunda.com/codes/show/6665/splitting-csv-string

Login to post response