admin Introduction
If you’re new to Oracle PL/SQL, string functions might seem intimidating at first, but the Oracle SUBSTR function is deceptively simple and immensely powerful. Whether you need to pull a customer’s area code from a phone number or isolate the year from a date-in-text, Oracle SUBSTR is your first stop. This guide walks through foundational concepts, hands-on examples, and common pitfalls, so beginners can extract substrings like seasoned professionals.
What Is SUBSTR?
At its core:
sql
CopyEdit
SUBSTR(source_string, start_position [, length])
- start_position: Positive = from left; Negative = from right.
- length: Number of characters; optional to go through end.
Getting Started: Simple Examples
Extract “SQL” from “Learn SQL Today”:
sql
CopyEdit
SELECT SUBSTR(‘Learn SQL Today’, 7, 3) FROM dual; — ‘SQL’
Omit length to extract from position 7 onward:
sql
CopyEdit
SELECT SUBSTR(‘Learn SQL Today’, 7) FROM dual; — ‘SQL Today’
Using Negative Start Positions
Grab the last three characters of “Oracle”:
sql
CopyEdit
SELECT SUBSTR(‘Oracle’, -3) FROM dual; — ‘cle’
Negative starts are a shortcut for LENGTH-based calculations.
Common Beginner Tasks
Area Code Extraction
Phone numbers in ‘(123) 456-7890’ format:
sql
CopyEdit
SELECT SUBSTR(phone, 2, 3) AS area_code
FROM contacts;
File Extension
From ‘report.pdf’:
sql
CopyEdit
SELECT SUBSTR(filename, INSTR(filename, ‘.’, -1) + 1) AS ext
FROM documents;
Initials from Names
From ‘John Doe’:
plsql
CopyEdit
v_name := ‘John Doe’;
v_initials := SUBSTR(v_name, 1, 1)
|| SUBSTR(v_name, INSTR(v_name, ‘ ‘) + 1, 1);
— ‘JD’
Handling Edge Cases
- Out-of-Bounds Start: If start_position > LENGTH, returns NULL.
- Length Exceeds: No error; returns through end.
- Multi-byte Characters: Works, but lengths count bytes; test Unicode.
Packaging into a Function
Turn repetitive logic into a reusable function:
pl
CopyEdit
CREATE OR REPLACE FUNCTION get_initials(p_fullname IN VARCHAR2)
RETURN VARCHAR2 IS
v_space_index NUMBER;
v_initials VARCHAR2(10);
BEGIN
v_initials := SUBSTR(p_fullname, 1, 1);
v_space_index := INSTR(p_fullname, ‘ ‘);
IF v_space_index > 0 THEN
v_initials := v_initials
|| SUBSTR(p_fullname, v_space_index + 1, 1);
END IF;
RETURN v_initials;
END;
Use it in queries:
sql
CopyEdit
SELECT employee_id,
get_initials(first_name || ‘ ‘ || last_name) AS initials
FROM employees;
Conclusion
With its straightforward syntax and flexible behavior, Oracle SUBSTR is a must-know function for any PL/SQL developer. By practicing simple examples, extracting area codes, file extensions, or initials, you’ll build fluency quickly. Remember to handle edge cases and package common patterns into functions for reusability. Armed with SUBSTR, you can tackle a wide range of text-processing tasks and elevate your PL/SQL skills from beginner to pro.
