|Lesson 4 || The |
| Objective || Understand the capabilities of the |
Upper and Lower SQL Functions
One can use this function to convert your string to all uppercase letters. For example, you could change computer to COMPUTER.
This can help when you are working with the names of states and need to make sure the abbreviation provided by the user is uppercase.
The syntax is simple:
You will get back a single, unnamed column with all the state abbreviations converted to uppercase.
As you probably guessed, the lower function will convert your string to all lowercase letters. For example, it will change DistributedNetworks to distributednetworks.
UPPER, the syntax is straightforward:
This returns a single, unnamed column with all the state abbreviations converted to lowercase.
Changing String Case with UPPER() and LOWER()
Use the function UPPER() to return a string with lowercase letters converted to uppercase, and use the function LOWER() to return a string with uppercase letters converted to lowercase. The functions’ important characteristics are:
- A cased character is a letter that can be lowercase (a) or uppercase (A).
- Case changes affect only letters. Digits, punctuation, and whitespace are left unchanged.
- Case changes have no effect on empty strings (' ' ).
- If its argument is null, UPPER() and LOWER() return null.
In DBMSs that perform case-sensitive WHERE-clause comparisons by default, UPPER() or LOWER() often is used to make case-insensitive comparisons:
WHERE UPPER(au_fname) = 'JOHN'
If you are sure that your data are clean, it is faster to look for only reasonable letter combinations than to use case functions:
WHERE au_fname = 'JOHN'
OR au_fname ='John'
UPPER() and LOWER() affect characters such as accents and umlauts:
UPPER(‘ö’) is ‘Ö’, for example.
If your data contain such characters and you are making case-insensitive comparisons such as
WHERE UPPER(au_fname) = 'JOSE'
make sure that your DBMS does not lose the marks on conversion.
UPPER('José') should be 'JOSÉ', not 'JOSE'.
Upper Lower - Exercise