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).

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.

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

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.