Parse Comma-Separated String to Make in List of Strings in the Where Clause

Parse comma-separated string to make IN List of strings in the Where clause

Create this function (sqlserver 2005+)

CREATE function [dbo].[f_split]
(
@param nvarchar(max), 
@delimiter char(1)
)
returns @t table (val nvarchar(max), seq int)
as
begin
set @param += @delimiter

;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @t
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end

use this statement

SELECT *
FROM yourtable 
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))

---

Comparing my split function to XML split:

Testdata:

select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a 
from master..spt_values a cross join master..spt_values b

XML:

 SELECT count(t.c.value('.', 'VARCHAR(20)'))
 FROM (
     SELECT top 100000 x = CAST('' + 
           REPLACE(txt, ',', '') + '' AS XML)
           from a
 ) a
 CROSS APPLY x.nodes('/t') t(c)

Elapsed time: 1:21 seconds

f_split:

select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')

Elapsed time: 43 seconds

This will change from run to run, but you get the idea

Using a comma separated varchar for a IN clause

You can't use the IN clause like that. It compiles to a single string in your IN clause. But an IN clause needs seperate values.

WHERE id in (@userId)

compiles to

WHERE id in ('1,4,65,12')

but it should be

WHERE id in (1,4,65,12)

If you really need the query to be dynamic then you can use

exec('SELECT * FROM UserTable WHERE Id IN (' + @userId + ')')

And you need to give your input parameter a length like

ALTER FUNCTION [dbo].[GetUser](@userId VARCHAR(1000))

In where condition split with comma separated string values

try this using STRING_SPLIT function.

select *
from employees
where employeeid in (select value from STRING_SPLIT ('PER5AZ,SF4MDD,WQERR', ','))

Note: STRING_SPLIT works SQL server 2016 or above

How to convert a c# comma separated values string into a list?

var intValues = line.Split(',').Select(x => Convert.ToInt32(x)).ToList();

Update

To ensure your code would be able to process strings like 1,2,3,,,4,5,6 you can use overload of String.Split method

var intValues = line.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries)
                    .Select(x => Convert.ToInt32(x))
                    .ToList();

How to send several strings separated by comma to be used in the WHERE IN clause through 1 SqlParameter?

Firstly, it's a little dangerous to rely on just commas, since your data may contain one, causing the only thing to break.

With that note, you could do the following:

string sqlText = @"
    SELECT col1, col3, col3              
    FROM table
    WHERE col1 IN ({0})";

string[] arrOfValues = GetListOfValues(userName).Split(',');
string[] paramArray = arrOfValues.Select((x, i) => "@value" + i).ToArray();
cmd.CommandText = string.Format(sqlText, string.Join(",", paramArray));

for (int i = 0; i < arrOfValues.Length; ++i)
{
    cmd.Parameters.Add(new SqlParameter("@value" + i, arrOfValues[i]));
}

Where IN a Comma delimited string

Create some split string function and convert the comma separated values to rows then you can use the converted rows IN clause

DECLARE @List VARCHAR(max)

SELECT @List = COALESCE(@List + ',', '') +StaffCode
FROM   tblStaffs

SELECT UserName
FROM   #temptable
WHERE  #temptable.StaffCode IN (SELECT split_values
                                FROM   dbo.Splitstring_function(@list)) 

Check here for various Split String function

If you dont want to create functions then you can also directly use the code instead of creating a new function(M.Ali's answer).

Another way of doing it is using dynamic query.

Declare @List varchar(max), @sql nvarchar(max)

Select @List = coalesce(@List + ',','') + '''' + StaffCode + ''''
From tblStaffs

set @sql = '
Select UserName
From #temptable
Where #temptable.StaffCode IN ('+ @List + ')'

--print @sql
exec (@sql)

To debug the dynamic query always print the dynamic sql before executing.

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

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

---

Related Topics