CODEWITHSUNDEEP
mysqlsql-server
Hitesh
Hitesh

Reputation: 1228

Using 'IN' clause for strings in Sql

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

I have data as below img in table. enter image description here

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')

Upvotes: 3

Views: 1742

Answers (4)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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:

enter image description here

Output:

enter image description here

Upvotes: 4

Rahul Khandelwal Systematix
Rahul Khandelwal Systematix

Reputation: 141

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

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

Upvotes: 0

Sergey Dudkin
Sergey Dudkin

Reputation: 189

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.

Upvotes: 0

Juozas
Juozas

Reputation: 935

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');

Upvotes: 0

Related Questions