MySQL Like statement not case-insensitive by default

Question

As far as I know, LIKE statements in MySQL are case-insensitive by default as shown here.

In my case, it seems they are not, for instance SELECT * FROM users WHERE CONCAT(name, ' ', surname) LIKE '%rob%' doesn't return anything, while SELECT * FROM users WHERE CONCAT(name, ' ', surname) LIKE '%Rob%' returns a record whose "name" column is "Robert".

I want SELECT * FROM users WHERE CONCAT(name, ' ', surname) LIKE '%rob%' to return the record whose "name" column is "Robert".

@Andreas Just updated the question, seems I'm not using LOWER() correctly with CONCAT() — gigapico00, Jun 09 '22 at 19:29
In the [docs](https://dev.mysql.com/doc/refman/8.0/en/charset-database.html#:~:text=To%20see%20the%20default%20character,SELECT%20DEFAULT_CHARACTER_SET_NAME%2C%20DEFAULT_COLLATION_NAME%20FROM%20INFORMATION_SCHEMA.), the says: use `SELECT @@character_set_database, @@collation_database;` to see the current collation. If that collation contains `ci` (case-insensitive) you will NOT have to use `UPPER` or `LOWER`. — Luuk, Jun 09 '22 at 20:25
Does this answer your question? [How can I search (case-insensitive) in a column using LIKE wildcard?](https://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard) — kmoser, Jun 10 '22 at 04:31

score 1 · Answer 1 · answered Jun 09 '22 at 19:45

1

SELECT * FROM users WHERE LOWER(CONCAT(name, ' ', surname)) LIKE '%rob%'

answered Jun 09 '22 at 19:45

JoelCrypto

462
2
12

But now if the user types "Rob" that query doesn't return anything – gigapico00 Jun 09 '22 at 20:00
So use REGEXP « rob ». % in like needs a character. – JoelCrypto Jun 09 '22 at 20:01

Akina · Answer 2 · 2022-06-10T04:19:21.920

As far as I know, LIKE statements in MySQL are case-insensitive by default as shown here.

The information described in this article is not correct. The case sensitivity in string operations depends on the collation.

MySQL 8.0 Reference Manual / Character Sets, Collations, Unicode.

CREATE TABLE test (txt_ai_ci VARCHAR(255) COLLATE utf8mb4_0900_ai_ci,
                   txt_as_cs VARCHAR(255) COLLATE utf8mb4_0900_as_cs);
INSERT INTO test VALUES
('GREEN', 'GREEN'),
('Green', 'Green'),
('green', 'green');

SELECT txt_ai_ci, 
       txt_ai_ci LIKE 'Gr%', 
       txt_as_cs, 
       txt_as_cs LIKE 'Gr%', 
       txt_as_cs COLLATE utf8mb4_0900_ai_ci LIKE 'Gr%'
FROM test
txt_ai_ci txt_ai_ci LIKE 'Gr%' txt_as_cs txt_as_cs LIKE 'Gr%' txt_as_cs COLLATE utf8mb4_0900_ai_ci LIKE 'Gr%'

GREEN 1 GREEN 0 1

Green 1 Green 1 1

green 1 green 0 1

db<>fiddle here

MySQL Like statement not case-insensitive by default

2 Answers2