Lesson 2 | Character (String) Functions |
Objective | Understand and apply character (string) functions unique to Oracle or with Oracle-specific behavior. |
Oracle Character (String) Functions
SQL provides built-in functions for manipulating character data (strings). These functions are essential for tasks such as:
- Concatenation: Combining two fields, literals, or expressions into a single string.
- Padding: Adding characters (e.g., spaces or dashes) to the beginning or end of a string to reach a desired length.
- Trimming: Extracting a portion of a string, such as the first few characters from a longer field.
String functions are particularly useful for:
- Formatting data for reports, form letters, or address labels where column data needs reformatting.
- Comparing parts of a column to an expression or literal in
WHERE
clauses or PL/SQL IF
statements.
- Converting data to a different format.
- Replacing specific characters or substrings with others, such as replacing spaces with hyphens.
This lesson focuses on key Oracle string functions, highlighting those unique to Oracle (e.g.,
INITCAP
,
SOUNDEX
) or with Oracle-specific behavior (e.g.,
REPLACE
with LOB support).
Character Functions
The table below lists Oracle’s character functions, their syntax, and descriptions, including those unique to Oracle or with Oracle-specific behavior.
Function |
Syntax |
Description |
CONCAT or || |
CONCAT(col1, col2) or col1 || col2 |
Concatenates one column with another. The || operator is standard SQL, but CONCAT is Oracle-supported. |
INITCAP |
INITCAP(col1) |
Capitalizes the first letter of each word (Oracle-specific). |
INSTR |
INSTR(col1, c) |
Returns the position of c in col1 . Oracle’s version supports additional parameters for occurrence and position. |
LENGTH |
LENGTH(col1) |
Returns the length of col1 . Supports multiple character sets in Oracle. |
LOWER |
LOWER(col1) |
Converts all letters to lowercase. Standard SQL, widely supported. |
LPAD |
LPAD(col1, n, c) |
Adds character c to the left of col1 until it is n characters long. Multiple characters can be used for c . |
LTRIM |
LTRIM(col1 [,set]) |
Removes characters in set from the left of col1 until a non-matching character is found. Default set is a space. |
REPLACE |
REPLACE(col1, old, new) |
Replaces all occurrences of old with new in col1 . Oracle’s version supports LOB datatypes (e.g., CLOB). |
RPAD |
RPAD(col1, n, c) |
Like LPAD , but adds characters to the right of col1 . |
RTRIM |
RTRIM(col1 [,set]) |
Like LTRIM , but removes characters from the right of col1 . |
SOUNDEX |
SOUNDEX(col1) |
Converts col1 to a phonetic code for comparing similar-sounding words (Oracle-specific). |
SUBSTR |
SUBSTR(col1, m [,n]) |
Returns a substring of col1 starting at position m for n characters. Oracle supports negative m for reverse indexing. |
TRANSLATE |
TRANSLATE(col1, old, new) |
Replaces individual characters in old with corresponding characters in new . Differs from REPLACE by handling one-to-one character mapping. |
UPPER |
UPPER(col1) |
Converts col1 to uppercase. Standard SQL, widely supported. |
Key Oracle String Functions with Examples
This section demonstrates practical uses of key Oracle string functions, focusing on those unique to Oracle (
INITCAP
,
SOUNDEX
) or with Oracle-specific behavior (
REPLACE
,
SUBSTR
,
INSTR
), alongside commonly used functions (
CONCAT
,
LPAD
).
REPLACE
(Oracle-specific LOB support)
Replaces all occurrences of a substring with another. Supports datatypes like CHAR, VARCHAR2, CLOB, etc.
SELECT REPLACE('JACK and JUE', 'J', 'BL') "Changes" FROM DUAL;
-- Output: BLACK and BLUE
In PL/SQL:
DECLARE
result VARCHAR2(100);
BEGIN
result := REPLACE('PL/SQL is great!', 'great', 'awesome');
DBMS_OUTPUT.PUT_LINE(result);
END;
-- Output: PL/SQL is awesome!
INITCAP
(Oracle-specific)
Capitalizes the first letter of each word in a string.
SELECT INITCAP('oracle database') "Formatted" FROM DUAL;
-- Output: Oracle Database
Use case: Formatting names or titles for reports.
SOUNDEX
(Oracle-specific)
Converts a string to a phonetic code for comparing similar-sounding words.
SELECT SOUNDEX('Smith') "Smith Code", SOUNDEX('Smyth') "Smyth Code" FROM DUAL;
-- Output: Smith Code: S530, Smyth Code: S530
Use case: Searching for names with similar pronunciation in a database.
SUBSTR
(Oracle-specific negative indexing)
Extracts a substring starting at position m
for n
characters. Oracle allows negative m
to count from the end.
SELECT SUBSTR('Oracle Database', 8, 8) "Normal", SUBSTR('Oracle Database', -8, 8) "Reverse" FROM DUAL;
-- Output: Normal: Database, Reverse: Database
Use case: Extracting file extensions or specific parts of a string.
INSTR
(Oracle-specific parameters)
Returns the position of a substring, with optional parameters for occurrence and starting position.
SELECT INSTR('Oracle Database Oracle', 'Oracle', 1, 2) "Second Occurrence" FROM DUAL;
-- Output: Second Occurrence: 17
Use case: Finding the position of a delimiter in a string.
CONCAT and ||
Combines two strings. The ||
operator is standard, but CONCAT
is Oracle-supported.
SELECT CONCAT('Oracle', ' Database') "Concat", 'Oracle' || ' Database' "Operator" FROM DUAL;
-- Output: Concat: Oracle Database, Operator: Oracle Database
Use case: Combining first and last names for display.
LPAD
Pads a string on the left with a specified character to reach a desired length.
SELECT LPAD('123', 5, '0') "Padded" FROM DUAL;
-- Output: Padded: 00123
Use case: Formatting numbers or codes with leading zeros.
Using Functions in SQL and PL/SQL
All listed functions are available in both SQL queries and PL/SQL blocks. For example, you can use
REPLACE
,
INITCAP
, or
SUBSTR
in a
SELECT
statement or within a PL/SQL procedure. Oracle’s support for advanced datatypes (e.g., CLOB for
REPLACE
) and unique features (e.g., negative indexing in
SUBSTR
) make these functions particularly powerful.
PL/SQL Example Combining Functions:
DECLARE
input VARCHAR2(100) := 'john doe';
result VARCHAR2(100);
BEGIN
result := LPAD(INITCAP(input), 12, '*');
DBMS_OUTPUT.PUT_LINE(result);
END;
-- Output: ****John Doe
Conclusion
Oracle’s character functions, such as INITCAP
, SOUNDEX
, and enhanced versions of REPLACE
, SUBSTR
, and INSTR
, provide powerful tools for string manipulation. These functions are versatile, usable in both SQL and PL/SQL, and support Oracle-specific features like LOB handling and phonetic matching. By mastering these functions, you can format data, extract substrings, and handle complex string operations efficiently.
In the next lesson, you will explore advanced uses of CONCAT
, SUBSTR
, LENGTH
, and INSTR
.
