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
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` (
`last_name` varchar(20) COLLATE latin1_general_cs,
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');
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';
SELECT salary FROM employees WHERE last_name = 'DOREEN' COLLATE latin1_general_ci;