1. Oracle SQL Character Functions

2. Overview • A Function is a type of formula whose result is one of two things: • A transformation • Such as changing a student’s name to upper case letters • Information • Such as finding the length of a word in a column • Functions are named and usually require at least one parameter • All functions in this section perform the transformation/information on a single row of the table • Aggregate functions perform against multiple rows

3. Data Types • Each value in a table row has a data type associated with it • A data type determines the value’s attributes and acceptable values • 123 is a number data type • “abc” is a text data type • “abc” is NOT a number data type • Most SQL operations use the following data types: • NUMBER • VARCHAR2 • DATE

4. Reading Function Syntax Diagrams Syntax Symbols

5. Character Functions Character Functions

6. Character Functions Character Functions continued

7. Character Functions Character Functions continued

8. Character Functions Practice • Write a query to show the city within the zipcode table as all capital letters, all lower case letters and mixed case letters • Use one query to show all three variations • Write a query to return the first 3 letters of a student’s last name from the student table • Write a query to display the length of each student’s last name • Using the CONCAT function, concatenate cities and states together (zipcode table) • Now use ||’s to add a comma and space • New York, NY • Using SOUNDEX list all last names that sound like ‘MARTIN’ (student table) • Hint: use a Where soundex (last_name) = soundex (‘MARTIN’) clause

9. For March 10th Number Functions Quiz Project 10 is due