sqlsql-serverstring-matchingcase-statement

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


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!


Solution

  • 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