Skip to main content

UPPER and LOWER clause

To conclude this discussion on functions, let us look at two you may find very useful. They do not perform calculations but convert character strings from lower case to upper case and vice versa.

It was mentioned previously that all strings are case sensitive, for example when searching for a surname of 'Jones', i.e.:

SELECT *

FROM employee

WHERE surname = 'Jones';

then if the surname is stored in the database as JONES it will not be found.

Similarly, if we wanted to use the LIKE clause to find all employees whose forename contains the characters 'ann', i.e.:

SELECT *

FROM employee

WHERE forename LIKE '%Ann%;

then this would find forenames such as 'Ann', 'Anne', 'Mary-Anne'. However, it would not find a forename of 'Joanne' as the case is different. To resolve this, the UPPER and LOWER functions can be used:

SELECT *

FROM employee

WHERE LOWER(surname) = 'jones';

or

SELECT *

FROM employee

WHERE UPPER(surname) = 'JONES';

SELECT *

FROM employee

WHERE LOWER(forename LIKE '%ann%;

or

SELECT *

FROM employee

WHERE UPPER(forename) LIKE '%ANN%;

Next: UPPER and L:OWER Expanded