How do I make my query only return names with a specific lower-case letter?

Question

SELECT *
FROM County
WHERE LOWER(Name) LIKE "%u%";

Im trying to return only rows where County names contain a lower case "u" somewhere in its name. For some reason with the query above I return several rows where Name only contain an upper case "U" -- which is not what I want. I dont understand... Thanks in advance!

Probably because you have a Case Insensitive collation on that column — RiggsFolly, Dec 07 '21 at 14:31
Start an SQL question by showing the schema for all the relevant tables. An example of the data contained in these tables is also very useful. If at all possible create a SQLFiddle with the schema defined and some test data provided — RiggsFolly, Dec 07 '21 at 14:33
That could be because you are using `LOWER` only during filtering (i.e., `USA` will be considered as `usa` and `Louisiana` will be considered as `louisiana`). However in select statement it will print the name as is (i.e., USA, Louisiana...). Now you might be wondering how USA getting filtered. — Arun, Dec 07 '21 at 14:41
@Henry Ecker: Thank you for the edit back to the original question. nostrad0muz, please don't mutilate your request. If you want to mark it as solved,, accept one of the answers by clicking the tick/check next to the answer. — Thorsten Kettner, Dec 08 '21 at 06:09

score 1 · Answer 1 · answered Dec 07 '21 at 14:51

Try :

SELECT * 
FROM County 
WHERE 
    BINARY name like '%u%' ;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=44048a2d080036ce9905340d6ebbf3e3

CREATE TABLE County (
Name varchar(30 ) 
 );

insert into County values
('Test1'),
('Test2'),
('Tust3'),
('TeAt4'),
('TeAt5'),
('TUst6'),
('Tust7');

Result:

Name
Tust3
Tust7

score 0 · Answer 2 · answered Dec 07 '21 at 14:55

mysql> show variables like '%character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | cp850                                                   |
| character_set_connection | cp850                                                   |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | cp850                                                   |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

Because my character_set_client is cp850, i can use the matching collating sequence latin1_general_cs. More info on this collating sequences if found in the documentation

SELECT *
FROM County
WHERE Name COLLATE latin1_general_cs LIKE "%u%"

Above query should find all records with a small letter u.

The collating sequence latin1_bin also works (as given in the other answers):

SELECT *
FROM County
WHERE Name COLLATE latin1_bin LIKE "%u%"

How do I make my query only return names with a specific lower-case letter?

2 Answers2