ExamFinch.com My Study

Study guide

4.1 Character Manipulation & Case Conversion

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

4.1 Character Manipulation & Case Conversion

Key Takeaways Explain This Topic AI

Case conversion functions (LOWER, UPPER, INITCAP) normalize string casing, with INITCAP capitalizing the initial letter of every word separated by whitespace or non-alphanumeric characters.

SUBSTR uses 1-based indexing (0 is treated as 1), negative starting positions count backward from the end, and non-positive lengths return NULL.

INSTR returns the 1-based starting position of a substring (or 0 if not found), supporting optional starting offset and occurrence count parameters.

TRIM removes only a single specified character from the leading, trailing, or both ends of a string, whereas LTRIM and RTRIM accept multi-character sets.

LPAD and RPAD pad strings to a total target length but truncate from the left if shorter, while REPLACE substitutes whole strings and TRANSLATE maps individual characters.

Last updated: August 2026

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:

One Result Per Row : They execute once for every row returned by the query and return a single result per row.

Flexible Input Types : They accept column values, literal constants, bind variables, or expressions as arguments.

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

Universal Clause Placement : They can be used in SELECT lists, WHERE predicates, ORDER BY clauses, START WITH / CONNECT BY hierarchical clauses, and HAVING filters.

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.

  • Function Syntax Description Example & Output
  • LOWER LOWER(str) Converts all characters to lowercase LOWER('SQL Course') → 'sql course'
  • UPPER UPPER(str) Converts all characters to uppercase UPPER('sql course') → 'SQL COURSE'

INITCAP INITCAP(str) Capitalizes the first letter of each word; lowercases all other letters INITCAP('jANE dOE-sMITH') → 'Jane Doe-Smith'

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-Based Indexing : The first character is at position 1.

Zero Position Rule : Specifying 0 for start_position is treated as 1 .

Negative Start Position : A negative start_position counts backward from the end of the string ( -1 is the last character).

Omitted Length : If length is omitted, SUBSTR extracts all remaining characters to the end of the string.

Non-Positive Length : If length is less than 1 (0 or negative), SUBSTR returns NULL .

  • 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:

Default mode is BOTH .

Default trim_char is a single space ' ' .

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

Function Common Exam Trap Correct Rule

CONCAT Passing 3 or more arguments CONCAT takes exactly 2 arguments; use || for multiple strings.

SUBSTR Expecting position 0 to be before position 1 Position 0 is automatically treated as position 1 .

SUBSTR Expecting negative length to extract backward Negative length returns NULL . Negative starting position extracts forward from backward offset.

INSTR Expecting NULL or -1 when a match fails INSTR returns integer 0 when the substring is not found.

TRIM Passing multiple trim characters ( TRIM('abc' FROM col) ) TRIM allows only one character. Use LTRIM / RTRIM for multi-character sets.

LPAD / RPAD Expecting padding when target length < string length Both functions truncate the source string to the target length from the left.

TRANSLATE Confusing word replacement with character replacement REPLACE matches whole substrings; TRANSLATE maps individual characters positionally.