Odtnhj

I have a database with the following data in the "Name" column:

However, running the following query returns incorrect results:

SELECT `e`.`Id`, `e`.`Name`
 FROM `Regions` AS `e`
 WHERE (LOCATE('s', `e`.`Name`) > 0)

Returns:

Calgary Northeast
Test Location
Calgary West

However its not returning "Southern BC" which has an s at the beginning. According to the MariaDB docs, the LOCATE function is case-insensitive (see: https://mariadb.com/kb/en/library/locate/), so I would expect "Southern BC" and "Calgary South" to show in the results. It's clear my query is doing a case sensitive comparison but I don't know why. All of my collations are set to utf8mb4_general_ci everywhere I have checked.

SELECT TABLE_SCHEMA
 , TABLE_NAME
 , TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME ='Regions';

returns utf8mb4_general_ci

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

returns

collation_connection utf8mb4_general_ci
collation_database utf8mb4_general_ci
collation_server utf8mb4_general_ci

I'm running 10.3.10-MariaDB-1:10.3.10+maria~bionic-log

Any idea on this behavior?

You can do either

SELECT `e`.`Id`, `e`.`Name`
 FROM `Regions` AS `e`
 WHERE (LOCATE('s', `e`.`Name`) > 0) OR (LOCATE('S', `e`.`Name`) > 0)

OR

SELECT `e`.`Id`, `e`.`Name`
 FROM `Regions` AS `e`
 WHERE e.name like '%S%' or e.name like '%s%'

I can't reproduce the problem:

MariaDB [_]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.3.10-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [_]> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.002 sec)

MariaDB [_]> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

MariaDB [_]> DROP TABLE IF EXISTS `Regions`;
Query OK, 0 rows affected (0.005 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `Regions` (
 -> `Name` VARCHAR(255)
 -> );
Query OK, 0 rows affected (0.010 sec)

MariaDB [_]> INSERT INTO `Regions`
 -> VALUES
 -> ('Calgary South'),
 -> ('Calgary Northeast'),
 -> ('Calgary Location'),
 -> ('Location'),
 -> ('Calgary West'),
 -> ('Calgary BC'),
 -> ('Southern BC');
Query OK, 7 rows affected (0.004 sec)
Records: 7 Duplicates: 0 Warnings: 0

MariaDB [_]> SELECT `Name`
 -> FROM `Regions`;
+-------------------+
| Name |
+-------------------+
| Calgary South |
| Calgary Northeast |
| Calgary Location |
| Location |
| Calgary West |
| Calgary BC |
| Southern BC |
+-------------------+
7 rows in set (0.000 sec)

MariaDB [_]> SELECT `Name`
 -> FROM `Regions`
 -> WHERE LOCATE('s', `Name`) > 0;
+-------------------+
| Name |
+-------------------+
| Calgary South |
| Calgary Northeast |
| Calgary West |
| Southern BC |
+-------------------+
4 rows in set (0.001 sec)

Please provide SHOW CREATE TABLE. The collation of name is significant.

If you are using the default collation or some other ..._ci collation, then this is suitable:

WHERE e.name LIKE '%s%'

to catch uppercase and lowercase s.

LIKE and REGEXP are appropriate for checking for substrings. LOCATE is appropriate for finding the position of something in a string.