How to check for a substring in a column, using CASE /IF (SQL querying)

Question

I need to return one of 2 values for certain conditions: My different cases are: when one column has 'substring' on the right end, return that column. (i.e. LIKE '%substring') Else return other column (from other table)

This works:

SELECT * From Table1 where col1 is not null and col1 like '%substring'

However, this doesn't work:

SELECT col5,
CASE Table1.Col1 
   WHEN NULL Then Table2.Col2
   WHEN '%substring' THEN Table1.Col1
   ELSE Table2.Col2
 FROM Table1
 JOIN Tablex ON Tablex.colID= Tabley.colID
 JOIN Table1 ON Table1.colID = Tablex.colID

and a bunch of more joins for interrelated tables with foreign keys with table1/table2... The results are always returned from the 'else' part.

I also tried this, this didn't work either:

SELECT col5,
CASE Table1.Col1 
   WHEN NULL Then Table2.Col2
   WHEN '' THEN Table2.Col2
   ELSE Table1.col1
 FROM Table1
 JOIN Tablex ON Tablex.colID= Tabley.colID
 JOIN Table1 ON Table1.colID = Tablex.colID

Answer 1

Need to use:

SELECT col5,
       CASE 
         WHEN Table1.Col1 IS NULL Then Table2.Col2
         WHEN Table1.Col1 LIKE '%substring' THEN Table1.Col1
         ELSE Table2.Col2
       END
FROM Table1
JOIN Tablex ON Tablex.colID= Tabley.colID
JOIN Table1 ON Table1.colID = Tablex.colID

CASE/WHEN expressions are equivalent to "=", so specifying NULL will never evaluate properly because you need to use IS NULL. Similarly, can't assume LIKE is being used...

You were also missing the END keyword to signal the end of the CASE expression.

Mind that Table2.Col2 and Table1.Col1 data types are the same too.