How to Make Like Clause Case-Insensitive

How can I search (case-insensitive) in a column using LIKE wildcard?

SELECT  *
FROM    trees
WHERE   trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'

Actually, if you add COLLATE UTF8_GENERAL_CI to your column's definition, you can just omit all these tricks: it will work automatically.

ALTER TABLE trees 
 MODIFY COLUMN title VARCHAR(…) CHARACTER 
 SET UTF8 COLLATE UTF8_GENERAL_CI. 

This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'

SQL SELECT LIKE (Insensitive casing)

use LOWER Function in both (column and search word(s)). Doing it so, you assure that the even if in the query is something like %VaLuE%, it wont matter

select qt.*
from query_table qt
where LOWER(column_name) LIKE LOWER('%vAlUe%');

How to make LIKE clause case-insensitive?

I assume you're using Postgres.

You can use ILIKE

Job.where('title ILIKE ? OR duration ILIKE ?', "%#{params[:search]}%", "%#{params[:search]}%")

Or a some tricky hack lower():

Job.where('lower(title) LIKE lower(?) OR lower(duration) LIKE lower(?)', "%#{params[:search]}%", "%#{params[:search]}%")

Perform a Case insensitive Like query in a case sensitive SQL Server database

You can use UPPER or LOWER functions to convert the values to the same case. For example:

SELECT *
FROM YourTable
WHERE UPPER(YourColumn) = UPPER('VALUE')

Alternatively, you can specify the collation manually when comparing:

SELECT *
FROM YourTable
WHERE YourColumn = 'VALUE' COLLATE SQL_Latin1_General_CP1_CI_AI

MySQL LIKE is case sensitive but I don't want it to be

Case sensitivity is based on the collation of the column you are searching, defined in your CREATE TABLE, or else the collation of the session, which determines the character set and collation of string literals.

Example:

CREATE TABLE `users_user` (
  `username` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into users_user set username='DEMO1-0048';

Here we see the default collation of utf8mb4_general_ci is case-insensitive:

mysql> select * from users_user where username like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

But if I force the column to use a case-sensitive collation:

mysql> select * from users_user where username collate utf8mb4_bin like 'DeMO1-0048';
Empty set (0.00 sec)

Or if I force the string literal to use a case-insensitive collation:

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_bin;
Empty set (0.00 sec)

Or if I define the table with a case-sensitive collation:

CREATE TABLE `users_user` (
  `username` text COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into users_user set username='DEMO1-0048';

mysql> select * from users_user where username like 'DeMO1-0048';
Empty set (0.00 sec)

So I would infer that your table is defined with a case-sensitive collation. You can check this:

mysql> select character_set_name, collation_name from information_schema.columns where table_name='users_user' and column_name='username';
+--------------------+----------------+
| character_set_name | collation_name |
+--------------------+----------------+
| utf8mb4            | utf8mb4_bin    |
+--------------------+----------------+

You can force a string comparison to be case-insensitive, even if the default collation defined for the table/column is case-sensitive.

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_general_ci;
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

This works if you use the collate option on the column too:

mysql> select * from users_user where username collate utf8mb4_general_ci like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

How to make LIKE behave case-insensitive?

This code works fine for me:

SELECT * 
  FROM adrp
  WHERE LOWER( name_first ) LIKE 'phi%'
  INTO TABLE @DATA(results).

It finds my personal data entry (as well as those of another "Philipp" and of a "Philip"), even though we are all spelled with a capital P.

LIKE LOWER( 'Phi%' ) does not work, but when you can't control the input, then you can convert it to lower case before the select:

DATA(tst_string) = 'Phi%'.
TRANSLATE tst_string TO LOWER CASE.
SELECT *
  FROM adrp
  WHERE LOWER( name_first ) LIKE @tst_string
  INTO TABLE @DATA(results).

Release: 7.54

I am not sure which release specifically allowed functions like LOWER within the WHERE clause. According to the comments, it should work since 7.51.

How do you force mysql LIKE to be case sensitive?

Use LIKE BINARY:

mysql> SELECT 'abc' LIKE 'ABC';
    -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
    -> 0

---

Related Topics