#1
  1. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123

    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 01:39 PM.
  2. #2
  3. No Profile Picture
    got Rice?
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Posts
    518
    Rep Power
    11
    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 03:02 PM.
  4. #3
  5. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123
    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.
  6. #4
  7. No Profile Picture
    got Rice?
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Posts
    518
    Rep Power
    11
    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
  8. #5
  9. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123
    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
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0
    Also you could use like, converting the INT TO char
    and adding , to the end and begining :

    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) + ',%'

IMN logo majestic logo threadwatch logo seochat tools logo