CODEWITHSUNDEEP
sql-serverstored-proceduressplit
Slim
Slim

Reputation: 131

SQL Server - How to select record where my value in split column

This is result that I want:

enter image description here

I wrote a stored procedure:

ALTER PROCEDURE [dbo].[test_Split] 
    @item INT
AS
    CREATE TABLE #temp
    (
         Id INT, 
         Name NVARCHAR(50), 
         List NVARCHAR(100)
    )

    INSERT INTO #temp 
    VALUES (1, 'John', '1,2,4,6,7'), (2, 'Fox', '7,23,42,10,17'),
           (3, 'Rose', '51,72,14,16,37'), (4, 'Alex', '1,22,84,56,47')

    SELECT * 
    FROM #temp

When I execute test_Split, I get the results shown in the screenshot.

When I pass @item = 1 (any number), I want to get result like the right part of the screenshot (List column include my value).

Is there any way to do this without a function?

Upvotes: 1

Views: 44

Answers (1)

S3S
S3S

Reputation: 25112

Just add

where List like + '%' + @item + '%'

So...

select * 
from #temp
where List like + '%' + @item + '%'

Notice this will find all rows where 1 is present. The only hack to distinguish between 1 and numbers like 15 is to search for a comma. 

select * 
from #temp
where 
List like + '%,' + @item + ',%'
or List like + @item + ',%'
or List like + '%,' + @item

It's much wiser to use a split function and INNER JOIN. I'd check out Jeff's article.

Upvotes: 1

Related Questions