Lesson 5	Character functions: UPPER, INITCAP, RTRIM, SOUNDEX
Objective	Describe the use of the character functions UPPER, INITCAP, RTRIM, and SOUNDEX.

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:

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

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

UPPER function: 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.
INITCAP: 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
```
The results look like this:
```
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.
RTRIM function: 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'
```
SOUNDEX: 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.
```
SELECT LASTNAME 
FROM CUSTOMER 
* WHERE SOUNDEX(LASTNAME) = SOUNDEX('Lea') 
SQL> /
LASTNAME 
----------
Lee
```

Character Functions - Quiz

Click the Quiz link below to answer a few questions about the character functions UPPER, INITCAP, RTRIM, and SOUNDEX.
Character Functions - Quiz
In the next lesson, you will learn how to use the number functions TO_CHAR, ROUND, and TRUNC.