Oracle SQL uppercase and lowercase

When working with SQL to retrieve data from your database you want it in some cases to be differently formatted than the data is in the database. The data in your database might not be as nice as you would like to present to users in a application. For example, you users expect data to be in uppercase or in lowercase while your data is not available in the database in such in way. Oracle has a couple of simple functions you can use to set the data exactly as you wish before creating the output

When you like to select text in all upper case you can use the command UPPER and to select all the text data in lower case you can use the command LOWER. For example we have the following query:

select
location_code,
location_name
from
noaa_weather_obsloc;

This will give:

LOCATION_CODE LOCATION_NAME
------------- ---------------
PAAK Atka Airport
PAAP Port Alexander
PAAQ Palmer
PABA Barter Island
PABE Bethel

5 rows selected

Now if we want to have all the location names in upper case we can use the query:

select
location_code,
upper(location_name)
from
noaa_weather_obsloc;

the upper command in front of location_name will make that all the characters returned from location_code are now in upper case.

LOCATION_CODE UPPER(LOCATION_NAME)
------------- ---------------
PAAK ATKA AIRPORT
PAAP PORT ALEXANDER
PAAQ PALMER
PABA BARTER ISLAND
PABE BETHEL

5 rows selected

The same can be used for lower case, however, now we have to use the command lower to make sure that all the characters are in lower case as in the query below is done.

select
lower(location_code),
location_name
from
noaa_weather_obsloc;

LOWER(LOCATION_CODE) LOCATION_NAME
------------- ---------------
paak Atka Airport
paap Port Alexander
paaq Palmer
paba Barter Island
pabe Bethel

5 rows selected


Now this is quite straight forward. However, You can expect for example a situation where you have a table with all names of people in it. In this case you would for example want to make sure that the every first letter of a word(name) is in uppercase and the rest is in lower case. For this Oracle has the INITCAP option which will set every first letter of a word in uppercase and the rest in lowercase. For example "aLberT EinStEIn" would make "Albert Einstein". Your data should be in a ideal situation never contain a name like this, however when you have them manualy enterd mistakes can be made and you can add this as a extra security to make sure your names are presented in a good way. You can see a example of Oracle SQL INITCAP below

select
INITCAP(location_code),
location_name
from noaa_weather_obsloc;