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

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!

CodePudding user response：

You can make the query using convert to varbinary – it’s very easy.

Example:

Select * 
from your_table 
where convert(varbinary, your_column) = convert(varbinary, 'u')

CodePudding user response：

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

CodePudding user response：

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