Case Statement in SQL using Like

Hello I have a SQL statement

INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode SELECT SystemTax.SystemTaxID, EmpowerCalendarCode.CalendarCodeID ,CASE WHEN EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3 WHEN EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2 WHEN EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4 ELSE 0 END FROM Foundation.Common.SystemTax SystemTax, Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode WHERE SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode and SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType 

Even though CalendarName has values like Quarterly (EOM) I still end up getting 0. Any ideas and suggestions!

3 Answers

For one, I would update your SQL to this so you are using a JOIN on your SELECT statement instead of placing this in a WHERE clause.

INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode SELECT SystemTax.SystemTaxID, EmpowerCalendarCode.CalendarCodeID ,CASE WHEN EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3 WHEN EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2 WHEN EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4 ELSE 0 END FROM Foundation.Common.SystemTax SystemTax INNER JOIN Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode ON SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode AND SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType 

two, what happens if you remove the INSERT INTO?

You can also do like this. I think this will work.

select * from table where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %' 

1. Try ruling-out any null issues with ISNULL():

2. Try ruling-out any case-sensitivity issues with UPPER():

   CASE WHEN upper(isnull(EmpowerCalendarCode.CalendarName, 'none')) LIKE '%MONTHLY%' THEN 3...