Check if a group of values is contained in a column in SQL Server

Question

I'm trying to check if a certain column has something like:

SELECT Id,
CASE CHARINDEX(Name,'Value A') WHEN 0 THEN '0'
      ELSE '1'
END
FROM TABLE

This works for testing Value A; now I want to check multiple values. For example, I'd like to check whether Name matches any of Value A, Value B, Value C. CHARINDEX will only allow one at a time.

There is a way to do this?

I want any of the values. Basically I want to check if a value from a list of values matches the column value. — JohnSmith82, Nov 15 '16 at 10:39

mendosi · Accepted Answer · 2016-11-15T11:11:15.123

1

SELECT id, 
    iif (Name LIKE '%Value A%' OR Name LIKE '%Value B%', 1, 0) AS IsContainsTheValue
  FROM Table;

This assumes that the values are static, which seems to be the case from your question.

Edit for exact match

Declare @matchValues Table (Value varchar(100));
-- maybe you could pass a table from the application??

SELECT id, 
    iif (v.Value Is Not Null, 1, 0) AS IsContainsTheValue
  FROM Table AS t
  LEFT JOIN @matchValues AS v On t.Name = v.Value;

edited Nov 15 '16 at 11:11

answered Nov 15 '16 at 10:42

mendosi

2,001
1
12
18

This works for me, I just need to build that iif statement outside my query because the number of values could be dynamic. thanks a lot – JohnSmith82 Nov 15 '16 at 10:47
Can I add a IN() in the select statement? – JohnSmith82 Nov 15 '16 at 10:48
`In ()` only works for exact matches, no `%` wild cards. – mendosi Nov 15 '16 at 10:49
Are the unknown number of values coming from a table in the db or from the application? – mendosi Nov 15 '16 at 10:50
It comes from the application – JohnSmith82 Nov 15 '16 at 10:51
I don't need the wild card, it can be exactly Value A, Value B – JohnSmith82 Nov 15 '16 at 10:51

Check if a group of values is contained in a column in SQL Server

1 Answers1