CODEWITHSUNDEEP
sqlsql-servert-sql
A.Pissicat
A.Pissicat

Reputation: 3275

How to do a IN from a list in string format

I'm working with SQL management Studio 2014. I have SQL table like :

sql_Type | sql_Name | sql_Parent

I want to get all sql_Type who have specific parent. My variable @listParents is nvarchar(MAX) and contains all parents separated with a ';'.

How can I do ? I have the beginning but I don't know what to write in my IN :

DECLARE @listParents nvarchar(MAX) = 'Parent1;Parent2;Parent3;';

SELECT sql_Name
FROM [myTable]
WHERE sql_Type = 'Box' AND sql_Parent IN
(
    -- What should I do wjth @listParents ?
)

Upvotes: 2

Views: 59

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

MSSQL (< SQL server 2016):

You can use CHARINDEX to solve this:

DECLARE @listParents nvarchar(MAX) = 'Parent1;Parent2;Parent3;'

SELECT sql_Name
FROM test
WHERE sql_Type = 'Box' AND CHARINDEX(sql_Parent + ';', @listParents) > 0;

demo: http://sqlfiddle.com/#!18/64a6b/10/0

MSSQL (>= SQL server 2016):

Since SQL server 2016 you can use STRING_SPLIT and IN to solve this:

DECLARE @listParents nvarchar(MAX) = 'Parent1;Parent2;Parent3;'

SELECT sql_Name
FROM test
WHERE sql_Type = 'Box' AND sql_Parent IN (
    SELECT value  
    FROM STRING_SPLIT(@listParents, ';')  
    WHERE RTRIM(value) <> ''
);

demo: http://sqlfiddle.com/#!18/64a6b/9/0

Upvotes: 4

Related Questions