Case Sensitive SQL Search

MySQL on default installation uses the case insensitive configuration meaning the data you stored and searched will not be case sensitive.

Let us first check our current server and database's appropariate variables.

SHOW VARIABLES
WHERE variable_name = 'character_set_server'

> latin1

SHOW VARIABLES
WHERE variable_name = 'collation_database'

> latin1_swedish_ci

Since we are using latin based language settings, the cases are different unlike some languages that don't have cases. And with the database collation set to latin_swedish_ci the string search will be case insensitive (ci).

For this illustration, I have created a table "country" with single column "country_name" and inserted 3 rows.

CREATE TABLE country (
     country_name varchar(32) NOT NULL
);
INSERT INTO country
VALUES ('US'), ('UK'), ('ukraine')
; 
COMMIT;

With the following SELECT I can look up the table and get all country names that begin with u or U.

SELECT country_name FROM country
WHERE country_name like 'u%';

I need to correct 'ukraine' to 'Ukraine' and can do so by the following statement.

UPDATE country
SET country_name = concat('U', substring_index(country_name, 'u', -1))
WHERE BINARY country_name like 'u%';
COMMIT;

NOTE the use of 'BINARY' in the where clause above.

This is a simple example which effectively demonstrates the use of case sensitive matching.
Enjoy,

Shiva

---

---