SQL Server: Pattern Matching in Case Statements with Multiple Possible Conditions-CodePudding

I am writing a query in SQL Server wherein I'm attempting to generate a data indicator based on multiple "fuzzy matched" criteria. Some example code to illustrate this task:

CASE WHEN COLUMN_1 IN ('CRITERIA_A') AND
     COLUMN_2 NOT LIKE '%FUZZY_MATCH_A%' OR
     COLUMN_2 NOT LIKE '%FUZZY_MATCH_B%' OR
     COLUMN_2 NOT LIKE '%FUZZY_MATCH_C%'
     THEN 'Y' ELSE NULL END

However, when this code runs the flag is assigned to all rows, when in fact is should only flag rows where COLUMN_2 doesn't match the patterns.

I attempted the use of parenthesis as a remedy like the following example, but it failed:

CASE WHEN COLUMN_1 IN ('CRITERIA_A') AND
     (COLUMN_2 NOT LIKE '%FUZZY_MATCH_A%' OR
      COLUMN_2 NOT LIKE '%FUZZY_MATCH_B%' OR
      COLUMN_2 NOT LIKE '%FUZZY_MATCH_C%')
     THEN 'Y' ELSE NULL END

I attempted nesting Case Statements as well, also failed:

CASE WHEN COLUMN_1 IN ('CRITERIA_A') THEN
     CASE WHEN COLUMN_2 NOT LIKE '%FUZZY_MATCH_A%' OR
          COLUMN_2 NOT LIKE '%FUZZY_MATCH_B%' OR
          COLUMN_2 NOT LIKE '%FUZZY_MATCH_C%'
          THEN 'Y' ELSE NULL END

So, written as if plainly-spoken, the goal is "When [COLUMN_1] has 'X' value and the value in [COLUMN_2] doesn't match this list of possible patterns then assign the record a flag of 'Y'".

Is this possible in SQL Server? I'd value any additional related expertise / insights you might offer as well. Sincere thanks in advance!

CodePudding user response：

If I understand the question correctly, you need to fix the boolean logic - replace all OR operators with AND:

SELECT 
   CASE 
      WHEN 
         COLUMN_1 IN ('CRITERIA_A') AND 
         COLUMN_2 NOT LIKE '%FUZZY_MATCH_A%' AND
         COLUMN_2 NOT LIKE '%FUZZY_MATCH_B%' AND
         COLUMN_2 NOT LIKE '%FUZZY_MATCH_C%' THEN 'Y' 
      ELSE NULL 
   END
FROM (VALUES
   ('CRITERIA_A', 'FUZZY_MATCH_A'),
   ('CRITERIA_A', 'FUZZY_MATCH_B'),
   ('CRITERIA_A', 'FUZZY_MATCH_C'),
   ('CRITERIA_A', 'FUZZY_MATCH_ABC'),
   ('CRITERIA_A', 'FUZZY_MATCH_D')
) t (COLUMN_1, COLUMN_2)

Result:

(No column name)
----------------
Null
Null
Null
Null
Y