Introduction
Concatenation
Simple text functions
12. Functions lower, upper, initcap – usage
How to modify strings
Revision

Instruction

Fine! The three functions we've just got to know: lower(), upper() or initcap(), can also be used to find some data, even if we're not sure about the letter case. This often happens when we work with data provided by our users, which are often messy: one person will use capital letters only, another one will use lower case, yet another will provide letters in a random case. Or another problem, we've got an item called riVer Flow for which we want to find the id, but we may not remember which letters were written with lower and upper case. What to do then? Take a look:

SELECT id
FROM item
WHERE lower(name)='river flow';

Smart, isn't it? Whatever the case of the name, we can simply put it in lower case and then compare it with 'river flow'. Of course, you could use upper(name)='RIVER FLOW' or initcap()='River flow' just as well.

By the way, this trick could be very useful if you had a chaotic database, with some column values written in small letters only, other with an initial capital letter and so on. Data aren't always consistent across all rows, so keep in mind our advice.

Exercise

Show the id of the item whose name written in upper case is TRIPCARE.

Stuck? Here's a hint!

In the WHERE clause, use upper(name)='TRIPCARE'.

Console

Code editor

Result

TableConsole