SQL Extensions   «Prev  Next»
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:
  1. Concatenation: Put two fields, literals, or expressions together into a single field.
  2. Padding: Add a character, such as a dash or a blank, to the beginning or end of a field to make it longer.
  3. 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:
  1. 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
  2. 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
  3. Converting data to a different format
  4. 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

In the next lesson, you will learn the use of the character functions CONCAT, SUBSTR, LENGTH, and INSTR.

Learn more about Oracle SQL and database design