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?

Answer 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;