Comma Delimited SQL string Need to separated

Question

I have this string that i am getting from .net application A,B,C,D,E,F,

I wanted to write a sql select statement like

set @string = 'A,B,C,D,E,F'

select * from tbl_test 
where tbl_test.code in (@string)

This wont work in t-SQL because it is using the @string as one string it is not separating the values. Is there any ways i can do this?

Answer 1

Nothing simple. You can write a function that will take in that list and split it apart into a table you can query against in the IN() statement.

Answer 2

A dynamic IN clause means either:

1. Converting the comma separated list into a temporary table to join onto
2. Using dynamic SQL (EXEC or EXEC sp_executesql)

Dynamic SQL example

---

DECLARE @SQL NVARCHAR(4000)

    SET @SQL = 'SELECT * FROM tbl_test t
                 WHERE t.code IN (@string_param)

BEGIN

  EXEC sp_executesql @SQL N'@string_param VARCHAR(100)', @string

END

Mind that sp_executesql is 2005+, and preferred because it will cache the query plan. Read The Curse and Blessings of Dynamic SQL for more detail, but be aware of SQL injection attacks.

Answer 3

Create a User Defined Function that takes the string as input and returns a table:

create function [dbo].[f_SplitString] (@str as varchar (1000))
returns @t table (value varchar (50))
etc...

Then adjust your Select statement:

select * from tbl_test 
where tbl_test.code in (select value from f_SplitString(@string))

Answer 4

3 options

1. Use a regular expression to replace the "," with "','" so that it becomes a proper ('A','B'...) list
2. Convert the list to XML and then parse the XML in your SELECT
3. Write a SPLIT function to convert comma delimited lists to tables

Answer 5

Very frequently asked question! What you want is a table-valued function.

But don't reinvent the wheel by writing your own, I found dozens just by Googling sql split. Here's one from Microsoft:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput

I used to use dynamic SQL for this, but that meant I had to dig up the code and copy it into each new app. Now I don't even have to think about it.

Answer 6

It think the easiest way to do it, will be, dynamic SQL generation:

// assuming select is a SqlCommand
string[] values = "A,B,C,D,E,F".Split(',');
StringBuilder query = new StringBuilder();
query.Append("select * from tbl_test where tbl_test.code in (");
int i = 0;
foreach (string value in values) {
    string paramName = "@p" + i++;
    query.Append(paramName);
    select.Parameters.AddWithValue(paramName, value);
}
query.Append(")");
select.CommandText = query.ToString();

// and then execute the select Command

Answer 7

Here is a function that returns a delimited String as a set of rows

set @string = 'A,B,C,D,E,F'      

select * from tbl_test       
where tbl_test.code in (select r from ftDelimitedAsTable(',',@string )    


  --/*----------------------------------------------------------------
    Create     FUNCTION [dbo].[ftDelimitedAsTable](@dlm char, @string varchar(8000))
    RETURNS 
    --------------------------------------------------------------------------*/
    /*------------------------------------------------------------------------
    declare @dlm  char, @string varchar(1000)
    set @dlm=','; set @string='t1,t2,t3';
    -- tHIS FUNCION RETUNRS IN THE ASCENDING ORDER
    -- 19TH Apr 06
    ------------------------------------------------------------------------*/
    --declare
        @table_var TABLE 
        (id int identity(1,1),
            r varchar(1000) 
         )
    AS
    BEGIN
    -- a.p --
    --Modified  18th Nov. 04

        declare @n int,@i int
        set @n=dbo.fnCountChars(@dlm,@string)+1
        SET @I =1
        while @I <= @N
            begin

                --print '@i='+convert(varchar,@i)+ ' AND INSERTING'
                insert @table_var
                    select dbo.fsDelimitedString(@dlm,@string,@i)
                set @I= @I+1

            end
    --PRINT '*************** ALL DONE'
        if @n =1 insert @TABLE_VAR VALUES(@STRING)
    --select * from @table_var
        delete  from @table_var where r=''
        return
    END


USE [QuickPickDBStaging]
GO
/****** Object:  UserDefinedFunction [dbo].[fsDelimitedString]    Script Date: 02/22/2010 12:31:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create function [dbo].[fsDelimitedString](
            @DelimiterStr varchar(100)
            ,@str varchar(4000)
            ,@pos int=1)
 returns varchar(4000)
as
/*
AP -- Dec 2003
Declare @DelimiterStr varchar(4000),@str varchar(4000) ,@pos int
set @delimiterStr = '-'
set @pos=10
set @str ='wd-1-22-333-4444-55555-666666-q-9'
*/
Begin
declare @rx varchar(4000)
set @rx=''; set @pos=@pos-1
IF DBO.fnCountChars(@DelimiterStr,@str) > 0 
    Begin
        if dbo.fnCountChars(@delimiterStr,@str) < @pos
        begin
            set @rx= null
            goto nulls
        end
        declare @i1 int,@tPos int,@ix int

        set @ix=1
        set @tPos=0
        while @tpos <> @pos
        Begin
            set @ix=charindex(@DelimiterStr,@str,@ix+1)
            if @ix > 0 set @tpos=@tpos+1
        end
        set @i1= charindex(@DelimiterStr,@str,@ix+1)
        if @i1=0 
                set @rx=substring(@str,@ix+1,len(@str)-@ix)
        else
            begin
                if @ix=1  
                    set @rx=substring(@str,@ix,@i1-@ix)
                else
                    set @rx= substring(@str,    @ix+1,@i1-@ix-1)        
            end
    --  'print 'ix='+convert(varchar,@ix)+' @i1='+convert(varchar,@i1)+' @rx='+@rx
        RETURN @RX
    end
nulls:  
    RETURN  @rx
end

Answer 8

You have several options:

• If you are OK with it, just compose the SQL statement dynamically before making call to SQL. There is a limitation on the number of values in the IN statement
• Use Table-valued UDF that splits the string and returns the table. Then your query would either use IN or better just JOIN statement (among other implementations I favor SQL User Defined Function to Parse a Delimited String).

The you code would be:

select     tbl_test.*
from       tbl_test 
inner join fn_ParseText2Table(@string) x
       on  tbl_test.code = x.txt_value 

• Since you use SQL Server 2005, you can write CLR Table-valued UDF that would do the same job as previous UDF does, which would be much smaller and faster since string operations in CLR are way better handled that in SQL.

Answer 9

You could keep it really simple with built-in sql functions:

set @string = 'A,B,C,D,E,F'

select * from tbl_test 
where CHARINDEX(ISNULL(tbl_test.code, 'X'), @string) > 0

PATINDEX can be used in case you need more than one character.

Answer 10

I've found http://www.sommarskog.se/arrays-in-sql.html to be a great in-depth list of various techniques one might want to use for things like this.