Splitting a comma separated string in SQL

[lpatnaik]

Hi,
I am working on SQL Server 2000. I am writing a stored procedure which is supposed to take as input a comm separated string such as "2,7,1,6"
Now the problem is that , I want my stored procedure to break it up into integers and insert one by one into a table .
1.e. i want to seprate the string into 2, 7, 1 and 6.
Can anyone tell how it can be done.
Thanks
LPatnaik

 

[essa2000]

dear ;

--first create this table for testing this script:

Create table testsplit
(
field1 varchar(2000)
)

--then run this script to check your task:


DECLARE @L_Param1 nvarchar(1000)
Declare @L_Param2 nvarchar(1000)
DECLARE @L_NewParam1 nvarchar(1000)
DECLARE @L_NoofCommas int
DECLARE @L_Count int
Declare @L_SQL nvarchar(4000)

SET @L_Param1 = Ltrim(Rtrim('a1,a2,a3'))
SET @L_Param2 = 'judy'
SET @L_SQL = ''
SET @L_NewParam1 = ''
SET @L_Count = 0

SET @L_NoofCommas = (Len(Ltrim(Rtrim(@L_Param1))) - Len(Replace(Ltrim(Rtrim(@L_Param1)) , ',' , '') )) + 1

WHILE @L_Count < @L_NoofCommas

BEGIN

SET @L_Count = @L_Count + 1

SET @L_NewParam1 =
CASE
WHEN Charindex( ',' , @L_Param1 ) > 0
THEN left(@L_Param1 , Charindex( ',' , @L_Param1 ) - 1)
WHEN Charindex( ',' , @L_Param1 ) = 0
THEN @L_Param1
END


PRINT @L_NewParam1 -- Optional

SET @L_SQL = 'INSERT INTO testsplit (Field1) Values ( '''+ @L_NewParam1 + ' ' + @L_Param2 + ''')'

Print @L_SQL -- Optional

Exec (@L_SQL)

SET @L_Param1 = Right( @L_Param1 , Len(@L_Param1) -
Len(left(@L_Param1 , Charindex( ',' , @L_Param1)))
)

PRINT @L_Param1 -- Optional

END


Print statements will explain you the flow of this script.

I hope it will work for you.
Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[r937]

how high do your integers go? if it's a small, manageable number, pull the integers out of an integer table

create table integers
( i integer );
insert into integers values(1);
insert into integers values(2);
insert into integers values(3);
etc.

now to insert your comma separated values into a new table,

insert into newtable
(targetcolumn)
select i from integers
where i in ( @csvstring )

make sure there are no quotes in the csvstring

rudy

http://rudy.ca/

 

[SQLSister]

This is what we use. Note we made it a user defined function so that we could reuse it in many places easily.

CREATE FUNCTION ParseCommaWords
	(@SearchPhrase nvarchar(255))

Returns @ParseWordsTable  Table 
	(record  nvarchar(50)	not null
	)

Begin

Declare @Length dec(6,2)
Declare @Record nvarchar(50)
Declare @WordMatch  nvarchar(50)
Declare @WordCount  int
Declare @Start  smallint



	Set @SearchPhrase = Ltrim(Rtrim(@SearchPhrase))
	Set @Length = len(@SearchPhrase)

	/*Check for multiple word search*/
			
	Set @Start = 1


	Set @WordCount = CHARINDEX (char(44),@SearchPhrase,@Start)

	If @WordCount <> 0
		begin
	
			While @WordCount < @Length
				begin						
					
					If @WordCount <> 0
						begin
							Set @WordMatch =  SUBSTRING(@SearchPhrase,@Start, @WordCount-@Start)

							Insert into @ParseWordsTable (Record)
							values(Ltrim(Rtrim(@WordMatch)))
	
							Set @Start = @WordCount+1
							Set @WordCount = CHARINDEX (char(44),@SearchPhrase,@Start)
						end	
					else
						If @Start > 1
							begin
								Set @WordMatch =   SUBSTRING(@SearchPhrase,@Start, cast(@Length as int)-@Start+1)

								Insert into @ParseWordsTable (Record)
								values(Ltrim(Rtrim(@WordMatch)))
								
								Set @WordCount = @Length

							end
				end	
		End 
	
         Return

END

 

[lpatnaik]

Thanks a lot. That has really helped me essa2000. Thanks to all of you, these solutions are great.

Lpatnaik