SQL ~ Split comma separated data and insert into a table

SQL ~ Split comma separated data and insert into a table

8

I want to insert a list of data that separated by comma like ‘1234,2345,3456’ into a SQL table. How do I do that? Well, it is not hard if you know the trick. Here are the codes to do it.

DECLARE @pos int, @curruntLocation char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = ‘1234,2345,3456’
SELECT @input = @input + ‘,’

CREATE TABLE #tempTable (temp varchar(100) )

WHILE CHARINDEX(‘,’,@input) > 0
BEGIN
SELECT @pos=CHARINDEX(‘,’,@input)
SELECT @curruntLocation = RTRIM(SUBSTRING(@input,1,@pos-1))
INSERT INTO #tempTable (temp) VALUES (@curruntLocation)
SELECT @input=SUBSTRING(@input,@pos+1,2048)
END

SELECT * FROM #tempTable

DROP TABLE #tempTable

Result – you will get 3 rows (1234,2345,3456) in #tempTable. The @input is your input variable. @curruntLocation is the value that needed to be inserted into SQL table. That’s it. :)

  • SC

    you can also put the string in an excel and use excel to generate the script

  • http://www.jayceooi.com Jayce

    @SC: How?

  • SC

    example given using excel 2007:

    copy the string to excel, excel will automatically convert the text to column by formatting it nicely, one value per column. Then you can transpose the columns to rows by using the Transpose function in Excel and voila, you are now able to get your data in rows.

    To generate the script, after performing the above steps, add the sql statement inside that excel and use & to append the sql statement with the value you wanna add in and you will get the final sql scripts that you want.

    If somehow in the first step when you try to copy the string to excel, excel does not convert the text to column to you, you can still use the Text to columns function provided by the Excel to achieve the same result.

  • Deb

    Hi Jayce,

    Your solution is really good… It helped…

    Thanks!

  • Juan

    Thanks, it helped so much

  • rujirek

    Works great! Thank you.

  • Mindwedgie

    Really like your solution. Much simpler than others I’ve seen on the web.
    The only thing I changed in my code was the select after the insert from:

    SELECT @input=SUBSTRING(@input,@pos+1,2048)

    to this

    SELECT @Input=SUBSTRING(@input,@pos+1,Len(@Input))

  • Mutik

    Thanks. You solution also works in Sybase ASE.