How to force a case insensitive select statement on a case sensitive column

Posted in: Articles by musiitwa joseph 2016-02-14 at 05:12:38

image for How to force a case insensitive select statement on a case sensitive column

Values inserted into a table can contain uppercase or/and lowercase letters. For a column that is case sensitive, if a SELECT SQL statement with a WHERE clause that contains an operand which varies in case with what is in the column of the table is made, no rows will be returned because case donot match. This can be solved by forcing all the letters to uppercase or lowercase, using UPPER() or LOWER() SQL functions on the operands or changing the collation of one of the operands, it does not affect the actual data in the table.

With the table below

Table name: employees

id first_name last_name salary
1 Kato John 1000000
2 Abaho Willy 6500000
3 Nakato Doreen 700000

Case sensitivity can be handled using the COLLATION, which are the rules for comparing characters. The default COLLATION for the database, tables and columns in MySQL is latin1_swedish_ci, all the searches are case insensitive. A column COLLATION can be changed to case sensitive as show in the table structure below.


CREATE TABLE `employees` (
  `id` int(11),
  `first_name` varchar(20),
  `last_name` varchar(20) COLLATE latin1_general_cs,
  `salary` int(11)
);

Assuming a select is made to find out DOREEN's salary, if the case does not match with what is in the table no rows will be returned. But the name DOREEN exists.


SELECT salary FROM employees WHERE last_name = 'DOREEN';

Let's now use the UPPER() or LOWER() function to solve this. The function must be applied to both operands.


SELECT salary FROM employees WHERE UPPER(last_name) = UPPER('DOREEN');

OR


SELECT salary FROM employees WHERE LOWER(last_name) = LOWER('DOREEN');

Changing COLLATION to latin1_general_ci


SELECT salary FROM employees WHERE last_name COLLATE latin1_general_ci = 'DOREEN';

OR


SELECT salary FROM employees WHERE last_name = 'DOREEN' COLLATE latin1_general_ci;

Tags:
SQL


Search

Leave a comment

Fields with * are required
Your email address will not be displayed
5 + 1 =