Using 'IN' clause for strings in Sql

Question

Hi I have table which have 2 column. Name varchar(50) and RoleType Varchar(500).

I have data as below img in table.

Now I am passing roletype 2 and 4 as parameter and want to get all data which have roletype either 2 or 4 in Roletype column. For example, result for 2 and 4 roletype are Test a, Test d, Test c and Test e.

I try with below query but it is not working.

SELECT * FROM Userinfo where Roletype in ('2', '4')

the real problem here is that you have a table badly in need of a redesign http://stackoverflow.com/a/41215681/267540 — e4c5, Dec 23 '16 at 10:57
break your comma separated id into rows ,in case if you don't want to redesign your table architecture ...then store that id's into temporary table and then perform operation on that temporary table — Ankit Agrawal, Dec 23 '16 at 11:18

score 4 · Accepted Answer · edited Feb 01 '17 at 17:08

Use Split function to split comma-separated column value:

Split function:

CREATE FUNCTION [dbo].[fnSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

Now update your query as below:

SELECT * 
FROM Userinfo 
WHERE EXISTS (SELECT Data 
              FROM [dbo].[fnSplit] (Roletype,',') 
              WHERE Data IN (2, 4) )

Example:

SELECT * 
FROM Category

SELECT * 
FROM Category C 
WHERE EXISTS (SELECT * 
              FROM [dbo].[fnSplit] (Name, ',') 
              WHERE DATA IN ('test', 'test3'))

Initial:

Output:

score 0 · Answer 2 · edited Dec 23 '16 at 15:03

0

Try to use the below query for getting your desired output -

SELECT
    *
FROM
    Userinfo
WHERE
    Roletype = '2'
OR Roletype = '4';

edited Dec 23 '16 at 15:03

Faisal

4,591
3
40
49

answered Dec 23 '16 at 10:59

Rahul Khandelwal Systematix

141
10

Err!! This will return 0 rows as per given OP's given. – Gurwinder Singh Dec 23 '16 at 11:03

score 0 · Answer 3 · answered Dec 23 '16 at 11:17

SELECT 
    * 
FROM 
    [Userinfo]  AS  [ui]
CROSS APPLY
    (
    SELECT 
        [str] = [X].[C].[value]('(./text())[1]', 'nvarchar(4000)')
    FROM 
        ( 
            SELECT 
                [X] = CONVERT(XML, '<i>' + REPLACE([ui].[roletype], ',', '</i><i>') + '</i>').query('.')
        )                   AS  [A] 
    CROSS APPLY 
        [X].[nodes]('i')    AS  [X]([C])
    )           AS  [s]         
WHERE 
    [s].[str] IN ('2', '4');

score 0 · Answer 4 · edited Dec 23 '16 at 12:03

0

Split solution from Sandip Patel is good. Still, you can use LIKE if you want.

You can write something like

SELECT
    *
FROM
    Userinfo
WHERE
    Roletype LIKE '%,2,%'
OR Roletype LIKE '2,%'
OR Roletype LIKE '%,2' etc.

edited Dec 23 '16 at 12:03

Faisal

4,591
3
40
49

answered Dec 23 '16 at 11:19

Sergey Dudkin

169
1
10

Using 'IN' clause for strings in Sql

4 Answers4