Top 5 Use Cases for Oracle Substr in PL/SQL Queriest
Introduction
Every PL/SQL developer encounters scenarios where parts of a string must be isolated, perhaps a SKU prefix, a user’s language code, or a status flag embedded in a comment. Oracle SUBSTR function excels at these tasks. In this article, we explore five of the most common, high-impact use cases for Oracle SUBSTR in real-world PL/SQL queries, complete with code examples and best practices.
- Extracting Fixed-Width Codes
Many legacy systems use fixed-width codes:
Example: Product code ‘PRD-2021-XYZ’:
plsql
CopyEdit
SELECT SUBSTR(prod_code, 1, 3) AS category,
SUBSTR(prod_code, 5, 4) AS year,
SUBSTR(prod_code, 10) AS identifier
FROM products;
- Tip: Document code layout centrally so everyone uses the same positions.
- Parsing Delimited Fields
Whether comma-separated tags or pipe-delimited logs:
First token:
plsql
CopyEdit
SELECT SUBSTR(field_value, 1,
INSTR(field_value, ‘,’) – 1) AS first_token
FROM data_table;
- Nth token: Loop with INSTR’s occurrence parameter in PL/SQL.
- Masking Sensitive Data
Show only last four digits of SSN:
plsql
CopyEdit
SELECT ‘XXX-XX-‘ || SUBSTR(ssn, -4) AS masked_ssn
FROM employees;
Negative start_position simplifies suffix extraction.
- Generating URL Slugs
Convert page titles to URL-friendly slugs:
plsql
CopyEdit
v_title := ‘Learn Oracle PL/SQL Today!’;
v_slug := LOWER(REPLACE(SUBSTR(v_title, 1, 50), ‘ ‘, ‘-‘));
Combine SUBSTR with REPLACE and LOWER for consistent slugs.
- Conditional Branching on Prefix
In reporting, branch logic based on a code prefix:
plsql
CopyEdit
DECLARE
v_code_prefix VARCHAR2(3);
BEGIN
v_code_prefix := SUBSTR(order_id, 1, 3);
IF v_code_prefix = ‘EXP’ THEN
— handle expedited orders
ELSIF v_code_prefix = ‘REG’ THEN
— handle regular orders
END IF;
END;
Use SUBSTR in CASE expressions within SQL for similar logic.
Conclusion
From parsing fixed-width identifiers to dynamically generating slugs, Oracle SUBSTR is the Swiss Army knife of string manipulation in PL/SQL. By mastering these five use cases, fixed-width codes, delimited fields, data masking, URL slugs, and prefix-based branching, you’ll solve many common challenges with concise, efficient code. Keep these patterns at hand, and you’ll be ready to handle any substring extraction requirement that comes your way.
