SQL to insert comma-delimited string value to table column

5 pts.
SQL queries
SQL Server development
SQL Server Query
I have string like '1,2,3,45,6,7,8' I want to insert each comma separated value in a table column...my output look like this: COLUMN 1 2 3 45 6 7 8

Answer Wiki

Thanks. We'll let you know when a new response is added.

This question can’t be answered without more information.

Which SQL server are you using? (MS SQL 2005, MySQL, SQLite, …)

What platform is it running on? (PC, Linux, …)

What application are you using to building your queries? (Visual Basic, C++, Access Query Builder, PHP, …)

How is the string being stored? (Text file, Spread Sheet, Web Form, …)

It’s looks like you’re asking two different questions.

How to instert data into columns. Which would be a simple…

SELECT INSERT INTO “Table1” (“column5”) VALUES (“First String Element”)

SELECT INSERT INTO “Table1” (“column5”) VALUES (“Second String Element”)

How you parse the input string depends on the language or application you are using.

But when you say, “My output look like this” it sounds like your talking about output formatting, which is entirely application specific.

The other problem is that SQL is not like a que, where data comes out in the same order it comes in. It’s more like a bucket full of thousands of different colored marbles. Even though the last one you tossed in was blue, that doesn’t mean the first one to fall out, if you tip it over, will be the blue one. SQL queries are like a robot that you are remotely asking to grab marbles in a specific order.

So once you’re input string is parsed into different SQL rows there’s no guarantee that your data will be retrieved in the same order it was stored in, unless you have another column to assist you in specifying that order, or the data conforms to some pattern that you can represent programmatically.


You could parse the string and insert the values into the table with a stored procedure, using the CHARINDEX and SUBSTRING functions..

Something similar to this:

<pre>CREATE PROCEDURE [dbo].[YourProcedure](@string varchar(50))
DECLARE @pos integer = 1,
@previous_pos integer = 0,
@value varchar(50)
WHILE @pos > 0
SET @pos = CHARINDEX(‘,’,@string,@previous_pos+1)
IF @pos > 0
SET @value = SUBSTRING(@string,@previous_pos+1,@pos-@previous_pos-1)
INSERT INTO YourTable VALUES (@value)
SET @previous_pos = @pos
IF @previous_pos < LEN(@string)
SET @value = SUBSTRING(@string,@previous_pos+1,LEN(@string))
INSERT INTO YourTable VALUES (@value)


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • msi77
    Erland Sommarskog gave exhaustive information in Arrays and Lists in SQL Server 2005.
    1,670 pointsBadges:
  • Npavvani
    hi carlosDL i was struggling for this solution from past 2 days ur procedure helped me a lot Thanks a lot i became member of this site after seeing ur solution Thanks a lot once again
    10 pointsBadges:
  • carlosdl
    Hi Npavvani. I'm glad it helped you. Welcome to ITKE.
    85,865 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: