SQL to insert comma-delimited string value to table column

5 pts.
Tags:
Comma-delimited
SQL
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))
AS
DECLARE @pos integer = 1,
@previous_pos integer = 0,
@value varchar(50)
BEGIN
WHILE @pos > 0
BEGIN
SET @pos = CHARINDEX(‘,’,@string,@previous_pos+1)
IF @pos > 0
BEGIN
SET @value = SUBSTRING(@string,@previous_pos+1,@pos-@previous_pos-1)
INSERT INTO YourTable VALUES (@value)
SET @previous_pos = @pos
END
END
IF @previous_pos < LEN(@string)
BEGIN
SET @value = SUBSTRING(@string,@previous_pos+1,LEN(@string))
INSERT INTO YourTable VALUES (@value)
END
END</pre>

-CarlosDL

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    Erland Sommarskog gave exhaustive information in Arrays and Lists in SQL Server 2005.
    1,670 pointsBadges:
    report
  • 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:
    report
  • carlosdl
    Hi Npavvani. I'm glad it helped you. Welcome to ITKE.
    70,220 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following