Why is this CASE statement in my T-SQL query not working properly?

I am running the following T-SQL code in SSMS but I am not getting the result I am expecting regarding the last column in the query:

 SELECT *, 
           (CASE WHEN Description like '%AI% '
                 THEN 'AI'
                   ELSE 'OTHER'
                     END) as 'Meal Plan'
 FROM TestTable

The 'Description' column from TestTable is an nvarchar column. An example of a value in that column would be 'STD AI REVENUE'.

From the query above, I expect the value of the 'Meal Plan' column to be 'AI' if the corresponding value in the Description column is 'STD AI REVENUE'. However, my query is filling the whole 'Meal Plan' column with 'OTHER'.

Thanks for your help!

Solution

Here is your current logic:

CASE WHEN Description like '%AI% '

This will match descriptions ending in a space. You probably intend to do this:

CASE WHEN Description like '%AI%'

Here is a demo showing the logic behind your query and my suggested correction:

Why is this CASE statement in my T-SQL query not working properly?

Rextester