Convert String into comma separated list for IN

Dev Shed Forums Sponsor:

September 25th, 2008, 11:45 AM

acidfourtyfive

Since 439000000

Join Date: May 2004

Location: Canada

Posts: 1,134

Time spent in forums: 1 Week 3 Days 23 h 30 m 11 sec
Reputation Power: 122

Convert String into comma separated list for IN

Hey everyone,

I was wondering if there was an easy way to convert a string into a list of numbers:

IE
"1,2,3,4" becomes 1,2,3,4

What I'm trying to do is take a comma separated list that is contained in a string, IE "1,2,3,4", and use it in an IN expression

For example:

Code:

DECLARE @teststring char(25) --actually comes in via stored procedure
...
where table.column in (@teststring)

Column is defined as an integer.

One way I thought of doing it was to do a replace on the string and replace all instances of , with ',' so that the items between the commas would be string. All I could have to do is append a ' to the start and end of the original string. THEN I would cast the integer column as a string.

So it would look something like

Code:

where cast(table.column as string) in ( '\'' + replace(@teststring, ',', '\',\'') + '\'')

I'm SURE this will not work as is, I will have to figure out the proper way to escape a string item in SQL.

Aside from this I found some google search results of some code that will split the string, which I could then convert each piece into an integer but I think that would then be difficult to put them into an IN list.

[EDIT]
This replace technique does not work. Any ideas will be greatly appreciated.
What I actually tried was:

Code:

where cast(test as char) in ('''' + replace(@test, ',', ''',''') + '''' )

It returns a string with the contents of '1','2','3','4' which is what I was hoping for but I was hoping that it would not be a string.

__________________

Last edited by acidfourtyfive : September 25th, 2008 at 12:39 PM.

September 25th, 2008, 01:56 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 509

Time spent in forums: 4 Days 7 h 39 m 38 sec
Reputation Power: 9

there might be a better way

but i have these 2 functions:

Code:

ALTER  FUNCTION [dbo].[fnList2Tablevarchar]
(
	@List varchar(MAX),
	@Delim char
)
RETURNS 
@ParsedList table
(
	item varchar(8000)
)
AS
BEGIN
	DECLARE @item varchar(8000), @Pos int

	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)

	WHILE @Pos > 0
	BEGIN
		SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
		IF @item <> ''
		BEGIN
			INSERT INTO @ParsedList (item) 
			VALUES (CAST(@item AS varchar(8000)))
		END
		SET @List = RIGHT(@List, LEN(@List) - @Pos)
		SET @Pos = CHARINDEX(@Delim, @List, 1)
	END

	RETURN
END

Code:

ALTER  FUNCTION [dbo].[fnList2Table]
(
	@List varchar(MAX)
)
RETURNS 
@ParsedList table
(
	item int
)
AS
BEGIN
	DECLARE @item varchar(800), @Pos int

	SET @List = LTRIM(RTRIM(@List))+ ','
	SET @Pos = CHARINDEX(',', @List, 1)

	WHILE @Pos > 0
	BEGIN
		SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
		IF @item <> ''
		BEGIN
			INSERT INTO @ParsedList (item) 
			VALUES (CAST(@item AS int))
		END
		SET @List = RIGHT(@List, LEN(@List) - @Pos)
		SET @Pos = CHARINDEX(',', @List, 1)
	END

	RETURN
END

then you can just use it in the from statement

Code:

SELECT		*
FROM		fnList2Tablevarchar(@InputValue,',')

or in your case,

Code:

SELECT		*
FROM		Table
WHERE someValue IN (SELECT * fnList2Table(@InputValue))

@InputValue is of course the passed in string list or whatever

I was told that this function isn't the most efficient way of doing it. maybe someone can correct me on this?

Last edited by jaeSun : September 25th, 2008 at 02:02 PM.

September 25th, 2008, 02:09 PM

acidfourtyfive

Since 439000000

Join Date: May 2004

Location: Canada

Posts: 1,134

Time spent in forums: 1 Week 3 Days 23 h 30 m 11 sec
Reputation Power: 122

I found a split function that takes the values and splits them based on a delimiter. It then returns a temporary table with these values.

September 25th, 2008, 02:17 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 509

Time spent in forums: 4 Days 7 h 39 m 38 sec
Reputation Power: 9

Quote:

Originally Posted by acidfourtyfive

I found a split function that takes the values and splits them based on a delimiter. It then returns a temporary table with these values.

can you post back with it? could be helpful for me and others too

September 25th, 2008, 02:49 PM

acidfourtyfive

Since 439000000

Join Date: May 2004

Location: Canada

Posts: 1,134

Time spent in forums: 1 Week 3 Days 23 h 30 m 11 sec
Reputation Power: 122

Here is the function I found on the net. I am a hardcore SQL noob. But I do know the stored procedure I wrote that uses this function takes 3 minutes nearly 4 on my core 2 duo with 4GB of ram to run. Although I do have VS.NET 2005 running as well as SQL server profiler. And between the ones that you added that I didn't notice before my previous post, someone with the same issue should have plent to choose from. Also note. I use this function about 25 times in my stored procedure. Approximately 5 times per 5 where clauses.

Code:

FUNCTION [dbo].[Split] 
   (  @Delimiter varchar(5), 
      @List      varchar(8000)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         SeparatedValue varchar(50) 
      ) 
AS 
   BEGIN
    
      DECLARE @LenString int 
 
      WHILE len( @List ) > 0 
         BEGIN 
         
            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 
                                
            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )
                
            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END

October 14th, 2012, 01:09 PM

csbened

Registered User

Join Date: Oct 2012

Posts: 1

Time spent in forums: 5 m
Reputation Power: 0

Also you could use like, converting the INT TO char
and adding , to the end and begining :

T-SQL Code:

Original - T-SQL Code
set @typelist=REPLACE(@typelist,' ','') if (LEFT(@typelist,1)<>',') set @typelist=','+@typelist if (RIGHT(@typelist,1)<>',') set @typelist=@typelist+',' delete from <table> where @typelist NOT LIKE '%,' + convert(varchar, type_id) + ',%'


set @typelist=REPLACE(@typelist,' ','')
if (LEFT(@typelist,1)<>',')
    set @typelist=','+@typelist
if (RIGHT(@typelist,1)<>',')    
    set @typelist=@typelist+','
delete from <table> where 
 @typelist NOT LIKE '%,' + convert(varchar, type_id) + ',%'

Convert String into comma separated list for IN

Developer Shed Advertisers and Affiliates