SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Split String & Insert Into Table Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BLarche
Starting Member

21 Posts

Posted - 04/17/2014 :  14:31:03  Show Profile  Reply with Quote
I have a string which is delimited by commas:

Green,Red,Blue,Orange,Black,White

I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).

For instance:

INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...

I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.


gbritton
Flowing Fount of Yak Knowledge

1446 Posts

Posted - 04/17/2014 :  14:56:10  Show Profile  Reply with Quote
If it's just one input string, the simplest thing is to pick it apart using charindex and substring, then use the pieces to insert your values. e.g.


declare @str varchar(1000) = 'Green,Red,Blue,Orange,Black,White'
declare @i int = 1
while len(@str) > 0 begin
    declare @comma int= charindex(',', @str)
    if @comma = 0 set @comma = len(@str)+1
    declare @color varchar(1000) = substring(@str, 1, @comma-1)
    INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (@id, 999, @color)
    set @str = substring(@str, @comma+1, len(@str))
    set @i +=1
end
Go to Top of Page

BLarche
Starting Member

21 Posts

Posted - 04/17/2014 :  15:31:16  Show Profile  Reply with Quote
This is being run in an ASP application. I will need to run some sort of function or stored procedure to update my DB?
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
110 Posts

Posted - 04/18/2014 :  04:02:05  Show Profile  Reply with Quote
May be something like this......


CREATE FUNCTION String(@Col2 VARCHAR(MAX))
RETURNS   @Result TABLE (Color nvarchar(4000))
AS
BEGIN
  DECLARE @startingposition INT
  DECLARE @InputString VARCHAR(50) = @Col2
  DECLARE @parts nvarchar(4000)
  SELECT @startingposition = 1
WHILE @startingposition !=0
  BEGIN
    SELECT @startingposition = CHARINDEX(',',@InputString)
     IF @startingposition !=0
        SELECT @parts = LEFT(@InputString,@startingposition - 1)
     ELSE
        SELECT @parts = @InputString
        INSERT INTO @Result(color) VALUES(@parts)
        SELECT @InputString = RIGHT(@InputString,LEN(@InputString) - @startingposition)
  END
RETURN;
END
DECLARE @TEMP TABLE (unique_id INT IDENTITY(1,1), ID INT, color VARCHAR(MAX))
INSERT INTO @TEMP(ID,color) SELECT 999 AS ID,color   FROM  dbo.string('Blue,Green,Red,Yello,White')
SELECT * FROM @TEMP



---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 04/18/2014 :  16:10:11  Show Profile  Reply with Quote
see this article I wrote on split string @ http://sqlsaga.com/sql-server/split-function-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000