Home > Enterprise >  MySQL Like statement not case-insensitive by default
MySQL Like statement not case-insensitive by default

Time:06-10

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".

CodePudding user response:

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

CodePudding user response:

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

Thу 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

  • Related