Character Functions: UPPER, INITCAP, RTRIM, SOUNDEX

This lesson focuses on four more of the character functions that are commonly used in SQL queries, PL/SQL blocks, and within applications where SQL or PL/SQL are used, such as Oracle Forms and Oracle Reports. The four functions are:

1. UPPER:Converts string to all upper case letters
2. INITCAP:Converts the first letter of each word to a capital letter
3. RTRIM:Removes unwanted characters from the right side of a string
4. SOUNDEX:Converts string to a phonetic equivalent

you will review examples of queries that use each of these functions in the subsections below.

The UPPER function can be useful when you want to compare search criteria to a string of text that contains a mixture of upper and lower case letters. Here is an example of a query that looks for the word "tank" in the PET_CARE_LOG data:

SELECT LOG_TEXT
FROM PET_CARE_LOG
WHERE UPPER(LOG_TEXT) LIKE '%TANK%'

You can use this function on incoming variables or parameters where the user enters a search string.

Similar to UPPER and LOWER, the INITCAP function changes the configuration of upper and lower case letters in your data. This might be useful in cases where you store customer names in all upper case (to facilitate indexing and searching) but you wish to print the data with initial capital letters on a form letter. Here is a quick example of a query using a column in the PET_CARE_LOG that contains user names in all upper case letters:

SELECT 'Dear ' || INITCAP(CREATED_BY_USER) || ', 
 you're great!'
AFFIRMATION
FROM PET_CARE_LOG

AFFIRMATION
-------------
Dear Henry, you're great!
Dear Mark, you're great!
Dear Janet, you're great!
Dear Janet, you're great!

Notice that the apostrophe listed in the results is shown as two apostrophes in the query. This is the standard way to tell SQL to print the apostrophe rather than viewing it as the beginning or end of a literal.

This function is most useful in situations where you store a fixed length character field (datatype CHAR) that contains trailing blanks. Get rid of the trailing blanks using RTRIM. Here is an example query. Assume that the column STATE_NAME is a CHAR datatype and is 40 characters long.

SELECT STATE_NAME 
FROM STATE_LOOKUP
WHERE RTRIM(STATE_NAME) = 'Wisconsin'

Here is an interesting function that uses an algorithm to convert any word into its phonetic equivalent so that you can compare two words that are spelled differently but sound alike. A good example is when you are searching for a person's last name and you do not know the exact spelling. The figure below shows you the query and the results using the pet store's CUSTOMER table.

In the next lesson, you will learn how to use the number functions TO_CHAR, ROUND, and TRUNC.

Click the Quiz link below to answer a few questions about the character functions UPPER, INITCAP, RTRIM, and SOUNDEX.
Character Functions - Quiz