Using the SQL LIKE operator with the UPPER() syntax

Question

I am trying to use the LIKE operator in my query as follows:

mysql> select cat_title from category where cat_title like '%Indian%Restaurant%';
+--------------------+
| cat_title          |
+--------------------+
| Indian_Restaurants |
+--------------------+
1 row in set (2.59 sec)

However, since I want to do a case insensitive search, I am trying:

mysql> select cat_title from category where UPPER(cat_title) like UPPER('%Indian%Restaurant%');
Empty set (2.83 sec)

Why is the second query not working?

Answer 1

Most likely the collation on the cat_title column is case insensitive. Use

... cat_title LIKE '%Indian%Restaurant%' COLLATE utf8_bin

See also

• How can I make SQL case sensitive string comparison on MySQL?
• Case Sensitive collation in MySQL

Answer 2

in all the sql there is ILIKE which is responsible to do case insensitive compression, so you can modify your query like below

select cat_title from category where cat_title ilike '%Indian%Restaurant%';

Answer 3

Not sure what's your question. The UPPER function won't work before a 'like' operator. If you want a case-insensitive search, use the operator 'like' only, since it's case-insensitive.

On the other hand, if you want a case-sensitive search, you may try using

   cat_title COLLATE Latin1_General_BIN LIKE '%Indian%Restaurant%'