Data Manipulation   «Prev  Next»

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:
  1. Concatenation: Combining two fields, literals, or expressions into a single string.
  2. Padding: Adding characters (e.g., spaces or dashes) to the beginning or end of a string to reach a desired length.
  3. Trimming: Extracting a portion of a string, such as the first few characters from a longer field.
String functions are particularly useful for:
  1. Formatting data for reports, form letters, or address labels where column data needs reformatting.
  2. Comparing parts of a column to an expression or literal in WHERE clauses or PL/SQL IF statements.
  3. Converting data to a different format.
  4. 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.

SEMrush Software 2 SEMrush Banner 2