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.

WHERE variable_name = 'character_set_server'

> latin1

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
VALUES ('US'), ('UK'), ('ukraine')
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%';

NOTE the use of ‘BINARY’ in the where clause above.

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s