Passing a Varchar Full of Comma Delimited Values to a SQL Server in Function

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

Using a comma-separated parameter in an IN clause

Try this one, Just need to add commas at the beginning and at the end of @params string.

Declare @params varchar(100) Set @params = ',param1,param2,param3,'

Select * from t
where CHARINDEX(','+cast(col1 as varchar(8000))+',', @params) > 0

SQL FIDDLE

SQL use in query for comma separated string

Just use a subquery:

select *
from mytable
where fruit in (select value from STRING_SPLIT(@MyStr, ','));

In older versions of SQL Server, you can use like:

select *
from mytable
where ',' + @MyStr + ',' like '%,' + fruit + ',%';

Select IN using varchar string with comma delimited values

There are several ways to handle this. One option is to use dynamic sql and inject your phone number string into a variable containing the statement and then executing that like this:

DECLARE @PhoneNumber VARCHAR(3000) 
SET @PhoneNumber = '6725556666,2124444444'
DECLARE @SQL NVARCHAR(max)
SET @SQL = N'
    SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
    FROM dbo.PhoneLog
    WHERE PhoneNumber IN (' + @PhoneNumber + ')'
EXEC sp_executesql @SQL

Please note that this approach can be vulnerable to SQL injection attacks, for instance feeding a string like

SET @PhoneNumber = '1);truncate table phonelog;--'

would effectively empty the table. So using a dynamic SQL approach like above should only be an option if it's certain that the string fed that in injected is sanitized and safe (or maybe it should never be used).

Another, possibly better, option is to use a user defined function to split the phonenumber variable and use that like this:

SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
FROM dbo.PhoneLog
WHERE PhoneNumber IN (
    SELECT splitdata FROM dbo.fnSplitString(@PhoneNumber,',')
    -- you could add a check here that the data returned from the function 
    -- is indeed numeric and valid
    -- WHERE ISNUMERIC(splitdata) = 1
    )

Here's the function used in the example:

CREATE FUNCTION [dbo].[fnSplitString]   
    (   
        @string NVARCHAR(MAX),   
        @delimiter CHAR(1)   
    )   
    RETURNS @output TABLE(splitdata NVARCHAR(MAX)   
    )   
    BEGIN   
        DECLARE @start INT, @end INT   
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)   
        WHILE @start < LEN(@string) + 1 BEGIN   
            IF @end = 0    
                SET @end = LEN(@string) + 1  

            INSERT INTO @output (splitdata)    
            VALUES(SUBSTRING(@string, @start, @end - @start))   
            SET @start = @end + 1   
            SET @end = CHARINDEX(@delimiter, @string, @start)  

        END   
        RETURN   
    END 

I did not write the function, I think I got it somewhere on the internet...

Create IN subquery from a VARCHAR parameter with comma separated values

WHERE CountryCode IN (SELECT *
                      FROM [dbo].[ufn_CSVToTable](@ExcludeCountries,',')

This is how you can create [dbo].[ufn_CSVToTable]:

How to convert comma separated NVARCHAR to table records in SQL Server 2005?

passing a comma-delimited string of values to a User Defined Function: towards using the IN tsql operator

You can create udf function for spiting sting into table

CREATE FUNCTION [dbo].[fn_ado_param] (@ado nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (ado nvarchar(4000))AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@ado)
         IF @chrind > 0
            SELECT @Piece = LEFT(@ado,@chrind - 1)
         ELSE
            SELECT @Piece = @ado
         INSERT @VALUES(ado) VALUES(@Piece)
         SELECT @ado = RIGHT(@ado,LEN(@ado) - @chrind)
         IF LEN(@ado) = 0 BREAK
      END
   RETURN
END

after that you can use it in your IN clause

select * from dbo.fn_ado_param('gold,silver,copper',',')

or in other plces

select * from anytable where somefield in (select * from dbo.fn_ado_param('gold,silver,copper',','))

How to pass a comma separated list to a stored procedure?

If you're using Sybase 12.5 or earlier then you can't use functions. A workaround might be to populate a temporary table with the values and read them from there.

---

Related Topics