| Lesson 2 | Character (string) functions |
| Objective | List character (string) functions unique to Oracle or different in Oracle. |
Oracle Character (string) functions
SQL provides built-in functions for common data manipulation. Some of these built-in functions include:
- Concatenation: Put two fields, literals, or expressions together into a single field.
- Padding: Add a character, such as a dash or a blank, to the beginning or end of a field to make it longer.
- Trimming: Pull out a portion of the data from a column, such as the first ten characters from a 30-character field.
SQL functions that operate on character fields (also called strings) are useful for many tasks, such as:
- Formatting data for output in reports, form letters, or address labels where the database column data is not stored exactly the way you want the output to appear
- Comparing a portion of the data in a column to an expression or literal; comparisons can be part of a
WHERE clause of a query, or part of an IF statement in PL/SQL
- Converting data to a different format
- Replacing one character of the data with a different character, such as replacing a blank space with a hyphen
Character functions
The table below lists all the character functions available in Oracle and what they do.
|
Function | Syntax | Description |
CONCAT or || | CONCAT(col1, col2) or Col1 || col2 | Concatenates one column with another |
INITCAP | INITCAP(col1) | Capitalizes first letter of each word |
INSTR | INSTR(col1, c) | Returns the position of (c) in col1 |
LENGTH | LENGTH(col1) | Returns the length of col1 |
LOWER | LOWER(col1) | Converts all letters to lower case |
LPAD | LPAD(col1, n, c) | Add character (c) onto the left side of col1, repeating it until col1 is (n) characters long. You can list multiple characters in (c). |
LTRIM | LTRIM(col1 [,set]) | Remove characters listed in (set) from the left side of col1 until a character that is not in the set is found. Default for (set) is a blank space. |
REPLACE | REPLACE(col1, old, new) | Replace all occurrences of the old character with the new character. You can include multiple characters in the (old) and (new) parameters; similar to TRANSLATE. |
RPAD | RPAD(col1, n, c) | See LPAD; this is the same except that it adds onto the right side of col1. |
RTRIM | RTRIM(col1 [,set]) | See LTRIM; this is the same except that it works its way from the left to right. |
SOUNDEX | SOUNDEX(col1) | Convert col1 to a phonetic version of the word; allows you to compare words that sound alike but are spelled differently. |
SUBSTR | SUBSTR(col1, m [,n]) | Returns a part of col1; start at character in position (m) and continue for a length of (n) |
TRANSLATE | TRANSLATE(col1, old, new) | Changes old to new in col1 |
UPPER | UPPER(col1) | Converts col1 to upper case |
REPLACE function
REPLACE(string1, match_string, replace_string)
Returns a string in which all occurrences of match_string in string1 are replaced by replace_string.
REPLACE is useful for searching a pattern of characters, and then changing all instances of that pattern in a single function call.
REPLACE function Purpose
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
If replacement_string is omitted or null, then all occurrences of search_string are removed.
If search_string is null, then char is returned. Both search_string and replacement_string, as well as char, can be any of the datatypes 1) CHAR, 2) VARCHAR2, 3) NCHAR, 4) NVARCHAR2,
5) CLOB, or 6) NCLOB. The string returned is in the same character set as char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings
Example: The following example replaces occurrences of J with BL:
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes
--------------
BLACK and BLUE
In the next lesson, you will learn the use of the character functions CONCAT, SUBSTR, LENGTH, and INSTR.