-1

I have a comma Separated value need to compare with a single value in SQL Server.

yyyyy = 5

DECLARE @sec varchar(max)
SET @sec = '2,3,4,5,6,89,52,36,14,';

SELECT * 
FROM XXX 
WHERE ','+yyyyy+',' LIKE '%' + @sec + '%'
Steve Py
  • 26,149
  • 3
  • 25
  • 43
ADITYA GUPTA
  • 12
  • 1
  • 5
  • 1
    please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and the solution is always the same, make from the string a row for every entry and search like you would every normalized table – nbk Aug 18 '20 at 17:15
  • 1
    You use a string splitting function to split your variable into a table and then use that table with `IN` - example in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15). If you don't have a current version of sql server, then simple search the internet for alternatives. – SMor Aug 18 '20 at 17:21

1 Answers1

1

Below the code requested:

DECLARE @sec varchar(max)

SET @sec = '2,3,4,5,6,89,52,36,14,';

;WITH CTE_SEC AS (
    SELECT value AS sec
    FROM 
        STRING_SPLIT(@sec, ',')  
    WHERE 
        RTRIM(value) <> ''
)
SELECT * 
FROM 
    XXX X
    INNER JOIN CTE_SEC C ON C.sec = X.yyyyy

STRING_SPLIT() function was introduced with SQL Server 2016

Gabriele Franco
  • 879
  • 6
  • 10