Case Sensitivity with "Like" in Proc SQL

[jlcalbre]

I ran into a situation where I wasn't getting the expected reults in a Proc SQL query. The line on my select statement was: "sum(case when table.name LIKE '%stove%' then 1 else 0 end) as TotalCount. I wasn't getting the correct results and discovered that the LIKE function is case-sensitive, causing the problem. However, I also discovered if I change the above line to "count(*) as TotalCount" and then use the like statement in the left join line: "LEFT JOIN table on table.id = root.id and table.name LIKE '%stove%' ", it isn't case-sensitive.

1st question, is there a way to make the LIKE function case-insenstive all the time? 2nd question, does anyone know why it would be case-sensitive in certain situations and not in others?

[Florent]

Hi,

Using the following statement will probably fit with your needs:

sum(case when upcase(table.name) LIKE '%STOVE%' then 1 else 0 end) as TotalCount

Regards,
Florent