4.1 Character Manipulation & Case Conversion

4.1 Character Manipulation & Case Conversion

Quick Answer: Single-row functions operate on individual rows and return exactly one result per row. Character functions fall into two categories: Case Conversion (LOWER, UPPER, INITCAP) and Character Manipulation (SUBSTR, INSTR, LENGTH, TRIM, LTRIM, RTRIM, LPAD, RPAD, REPLACE, TRANSLATE, CONCAT). Key 1Z0-071 rules: string positions are 1-based (position 0 is treated as 1), SUBSTR with a negative starting position counts from the end, INSTR returns 0 if the substring is not found, TRIM accepts only one trim character, and LPAD/RPAD truncate strings when the target length is shorter than the source string.

---

Overview of Single-Row Functions

In Oracle SQL, functions are categorized into single-row functions and multiple-row (aggregate/group) functions.

Core Properties of Single-Row Functions:

1. One Result Per Row: They execute once for every row returned by the query and return a single result per row.
2. Flexible Input Types: They accept column values, literal constants, bind variables, or expressions as arguments.
3. Datatype Transformation: They can return a value of a different datatype than their input arguments (e.g., LENGTH('Oracle') takes a string and returns a number).
4. Universal Clause Placement: They can be used in SELECT lists, WHERE predicates, ORDER BY clauses, START WITH/CONNECT BY hierarchical clauses, and HAVING filters.
5. Arbitrary Nesting: They can be nested to any depth: F3(F2(F1(column))).

---

Case Conversion Functions

Case conversion functions normalize text casing across VARCHAR2, CHAR, and CLOB datatypes. They are essential for case-insensitive filtering in WHERE clauses.

The INITCAP Word Delimiter Rule

INITCAP identifies word boundaries using whitespace and any non-alphanumeric character (such as hyphens, periods, underscores, slashes, and quotes):

SELECT INITCAP('oracle-certified_associate 2026.exam') AS formatted_title
FROM dual;
-- Output: Oracle-Certified_Associate 2026.Exam

SELECT INITCAP('o''connor/mc_donald') AS formatted_name
FROM dual;
-- Output: O'Connor/Mc_Donald

Case-Insensitive Searching in WHERE Clauses

Because Oracle string comparisons are strictly case-sensitive, case conversion functions prevent filter mismatches:

-- Matches 'King', 'KING', 'king', 'kInG'
SELECT employee_id, last_name, salary
FROM employees
WHERE UPPER(last_name) = 'KING';

[!NOTE] Applying a function to a column in a WHERE clause (such as WHERE UPPER(last_name) = 'KING') prevents Oracle from using a standard B-tree index on last_name, resulting in a full table scan unless a Function-Based Index (CREATE INDEX idx_emp_upper_lname ON employees(UPPER(last_name));) is defined.

---

Character Manipulation Functions

Character manipulation functions inspect, extract, alter, or format string contents.

1. CONCAT(str1, str2)

Concatenates two character strings together. It is strictly limited to exactly two arguments.

SELECT CONCAT('Oracle ', 'Database') FROM dual; -- Returns 'Oracle Database'

-- INVALID: CONCAT takes only 2 parameters. Triggers ORA-00909: invalid number of arguments
-- SELECT CONCAT('Oracle', ' ', 'SQL') FROM dual;

-- Use the concatenation operator (||) for 3 or more strings:
SELECT 'Oracle' || ' ' || 'SQL' || ' 1Z0-071' FROM dual; -- Returns 'Oracle SQL 1Z0-071'

2. LENGTH(str)

Returns the number of characters in a string as an integer. If the input is NULL, LENGTH returns NULL.

SELECT LENGTH('Oracle SQL') AS len1, LENGTH('') AS len2, LENGTH(NULL) AS len3
FROM dual;
-- Returns: len1 = 10, len2 = NULL, len3 = NULL

[!IMPORTANT] In Oracle SQL, an empty string '' is treated as NULL. Therefore, LENGTH('') evaluates to NULL, not 0.

3. SUBSTR(str, start_position [, length])

Extracts a substring of a specified length starting at start_position.

Detailed SUBSTR Rules:

1. 1-Based Indexing: The first character is at position 1.
2. Zero Position Rule: Specifying 0 for start_position is treated as 1.
3. Negative Start Position: A negative start_position counts backward from the end of the string (-1 is the last character).
4. Omitted Length: If length is omitted, SUBSTR extracts all remaining characters to the end of the string.
5. Non-Positive Length: If length is less than 1 (0 or negative), SUBSTR returns NULL.
6. Out-of-Bounds Start: If start_position exceeds the string length, SUBSTR returns NULL.

SELECT 
  SUBSTR('ORACLE9I', 1, 6)   AS ex1,  -- 'ORACLE'
  SUBSTR('ORACLE9I', 0, 6)   AS ex2,  -- 'ORACLE' (0 treated as 1)
  SUBSTR('ORACLE9I', 7)      AS ex3,  -- '9I'     (extracts to end)
  SUBSTR('ORACLE9I', -2)     AS ex4,  -- '9I'     (starts 2 from end)
  SUBSTR('ORACLE9I', -5, 3)  AS ex5,  -- 'CLE'    (starts at 'C', takes 3 chars)
  SUBSTR('ORACLE9I', 4, 0)   AS ex6,  -- NULL     (length 0 yields NULL)
  SUBSTR('ORACLE9I', 4, -2)  AS ex7,  -- NULL     (negative length yields NULL)
  SUBSTR('ORACLE9I', 20, 2)  AS ex8   -- NULL     (start position beyond string)
FROM dual;

4. INSTR(string, substring [, start_position [, occurrence]])

Searches string for substring and returns the 1-based integer position where the match begins. Returns 0 if no match is found.

Detailed INSTR Rules:

• start_position: Starting search index (default = 1). A negative position instructs Oracle to search backward (right-to-left) from that offset.
• occurrence: Which occurrence to find (default = 1). Must be positive (> 0).
• Return Value: The returned position is always measured from the start of the string (1-based), regardless of whether the search moved left-to-right or right-to-left.

SELECT
  INSTR('CORPORATE FLOOR', 'OR')        AS pos1, -- 2 (1st occurrence from pos 1)
  INSTR('CORPORATE FLOOR', 'OR', 3)     AS pos2, -- 5 (1st occurrence searching from pos 3)
  INSTR('CORPORATE FLOOR', 'OR', 1, 2)  AS pos3, -- 5 (2nd occurrence)
  INSTR('CORPORATE FLOOR', 'OR', 1, 3)  AS pos4, -- 14 (3rd occurrence in 'FLOOR')
  INSTR('CORPORATE FLOOR', 'OR', -1)    AS pos5, -- 14 (1st occurrence searching backward from end)
  INSTR('CORPORATE FLOOR', 'OR', -5, 2) AS pos6, -- 2  (2nd occurrence searching backward from pos -5)
  INSTR('CORPORATE FLOOR', 'XYZ')       AS pos7  -- 0  (Not found returns 0, NOT NULL)
FROM dual;

5. TRIM([ [ LEADING | TRAILING | BOTH ] [ trim_char FROM ] ] source_string)

Removes leading, trailing, or both occurrences of trim_char from source_string.

Detailed TRIM Rules:

1. Default mode is BOTH.
2. Default trim_char is a single space ' '.
3. Single Character Limitation: trim_char must be exactly one character. Passing a multi-character string generates ORA-30001: trim set should have only one character.

SELECT
  TRIM('   Oracle SQL   ')                      AS t1, -- 'Oracle SQL'
  TRIM(LEADING '0' FROM '0001234500')           AS t2, -- '1234500'
  TRIM(TRAILING '0' FROM '0001234500')          AS t3, -- '00012345'
  TRIM(BOTH 'x' FROM 'xxxDatabase Masterxxx')   AS t4, -- 'Database Master'
  TRIM('x' FROM 'xxxDatabase Masterxxx')        AS t5  -- 'Database Master' (BOTH is default)
FROM dual;

6. LTRIM and RTRIM

LTRIM(str [, set]) and RTRIM(str [, set]) remove all characters appearing in set from the left or right of str until a character not present in set is reached.

[!NOTE] Unlike TRIM, LTRIM and RTRIM accept multi-character sets and use comma-separated argument syntax rather than the FROM keyword.

-- Strips any combination of 'x', 'y', and 'z' from the left
SELECT LTRIM('xyxzzyTest String', 'xyz') FROM dual; -- Returns 'Test String'

-- Strips trailing numbers and periods
SELECT RTRIM('Product Code 99.80', ' 0123456789.') FROM dual; -- Returns 'Product Code'

7. LPAD and RPAD: Padding and Truncation Mechanics

LPAD(str, total_length [, pad_str]) and RPAD(str, total_length [, pad_str]) return str formatted to total_length characters, filled on the left or right with pad_str (defaults to space).

Truncation Behavior:

If total_length is smaller than LENGTH(str), both LPAD and RPAD truncate the string to total_length characters starting from the left.

SELECT
  LPAD('Salary', 10, '*')   AS p1, -- '****Salary' (padded on left to 10 chars)
  RPAD('Salary', 10, '*-')  AS p2, -- 'Salary*-*-' (padded on right with repeating '*-')
  LPAD('Enterprise', 5, '*') AS p3, -- 'Enter'      (truncated to first 5 chars!)
  RPAD('Enterprise', 5, '*') AS p4  -- 'Enter'      (truncated to first 5 chars!)
FROM dual;

8. REPLACE vs. TRANSLATE

• REPLACE(text, search_str [, replace_str]): Searches for exact occurrences of the substring search_str and replaces them with replace_str. If replace_str is omitted or NULL, all occurrences of search_str are removed.
• TRANSLATE(text, from_chars, to_chars): Performs character-by-character substitution based on positional correspondence. If from_chars has more characters than to_chars, the extra characters are deleted from the result.

-- REPLACE: Replaces entire '123' token with 'ABC'
SELECT REPLACE('12345 123 9123', '123', 'ABC') FROM dual;
-- Output: 'ABC45 ABC 9ABC'

-- REPLACE without 3rd parameter removes search_str
SELECT REPLACE('A-B-C-D', '-') FROM dual;
-- Output: 'ABCD'

-- TRANSLATE: 1-to-1 character mapping (1->A, 2->B, 3->C)
SELECT TRANSLATE('12345 123 9123', '123', 'ABC') FROM dual;
-- Output: 'ABC45 ABC 9ABC'

-- TRANSLATE character removal: '1'->'A', '2' and '3' have no target -> removed
SELECT TRANSLATE('12345 123 9123', '123', 'A') FROM dual;
-- Output: 'A45 A 9A'

---

Summary of 1Z0-071 Character Function Traps