Check if a number is in an array, which is the content of a SQL database field

Question

A SQL database field has an array as the content (comma separated values, all integer numbers). I need to check if a number is in this array and, if yes, then the record is filtered on a select query.

A simple solution would be: suppose a function like 'Is_In' below:

select * from table where @number Is_In([fieldWithArrayContent])

I expect there is a SQL in function or even a function that can be written and used to solve this issue.

Possible duplicate of [SQL Server + Select WHERE IN comma delimited string](https://stackoverflow.com/questions/18028881/sql-server-select-where-in-comma-delimited-string) — sticky bit, Apr 09 '19 at 19:34
[This answer](https://stackoverflow.com/a/48209876/5089204) provides two approaches. 1) You can create the statement as a string and use `EXEC()` to execute it. 2) You can use any splitting approach to get a derived set out of your CSV. This can be done with various functions (`STRING_SPLIT()` needs v2016+, but there are many examples around). Or you can use one of the various inline splitter like the well known XML approach or a recursive CTE. — Shnugo, Apr 10 '19 at 07:56
Thank you for the responses. I like Alan Burstein´s response... — Claudio.hz, Sep 17 '19 at 17:15

score 0 · Answer 1 · answered Apr 09 '19 at 19:47

You need a splitter function - for best performane I suggest DelimitedSplit8k. Then you could just do this:

-- Sample Data 
DECLARE @sometable TABLE (someid INT IDENTITY, someArray VARCHAR(1000));
INSERT @sometable(someArray)
VALUES('1,2,10,12'),('5,6,7'),('10,12,10,20,10,10'),('1,2,3'); -- id 1 & 3 have the value "10"

-- Variable
DECLARE @number INT = 10;

SELECT DISTINCT t.someid, t.someArray
FROM   @sometable AS t
CROSS APPLY dbo.delimitedSplit8k(t.someArray,',') AS s
WHERE @number = s.item;

Returns:

someid      someArray
----------- ------------------------
1           1,2,10,12
3           10,12,10,20,10,10

score 0 · Answer 2 · answered Apr 11 '19 at 17:03

Using the same example as Alan, we can do that without a splitting function with some string manipulation as below:-

DECLARE @sometable TABLE (someid INT IDENTITY, someArray VARCHAR(1000));
INSERT @sometable(someArray)
VALUES('1,2,10,12'),('5,6,7'),('10,12,10,20,10,10'),('1,2,3'); -- id 1 & 3 have the value "10"

-- Variable
DECLARE @number INT = 1 --or 10 will work
Declare @seperator varchar(1)=','
Declare @search varchar(50)=CONCAT('%',@seperator,cast(@number as varchar(10)),@seperator,'%')

SELECT  t.someid, t.someArray
FROM   @sometable AS t
WHERE CONCAT(@seperator,someArray,@seperator) like @search

Check if a number is in an array, which is the content of a SQL database field

2 Answers2