Introduction
String manipulation is at the heart of many database-driven applications. Whether you’re parsing codes, extracting meaningful tokens from free-text comments, or trimming identifiers to a fixed length, Oracle SUBSTR function is your go-to tool. In this article, we’ll dive deep into practical PL/SQL examples that demonstrate how Oracle SUBSTR can be used to solve common tasks, from fixed-width parsing to dynamic token extraction, so you can write cleaner, more efficient code in your daily projects.
Understanding SUBSTR Syntax
The basic signature of the SUBSTR function is:
sql
CopyEdit
SUBSTR(source_string, start_position [, substring_length])
- source_string: The string to extract from.
- start_position: The 1-based index where extraction begins. Negative values count from the end.
- substring_length (optional): Number of characters to return. Omit it to extract through the end.
Example 1: Fixed-Width Field Parsing
Imagine you have a log table with a fixed-width code:
sql
CopyEdit
CREATE TABLE log_entries (
entry_id NUMBER,
raw_code VARCHAR2(20)
);
INSERT INTO log_entries VALUES (1, ‘ABC123XYZ456DEF’);
To extract the first three characters (the “category”):
plsql
CopyEdit
DECLARE
v_category VARCHAR2(3);
BEGIN
SELECT SUBSTR(raw_code, 1, 3) INTO v_category
FROM log_entries
WHERE entry_id = 1;
DBMS_OUTPUT.PUT_LINE(‘Category: ‘ || v_category); — ABC
END;
Similarly, to pull the middle batch number (positions 4–6):
sql
CopyEdit
SUBSTR(raw_code, 4, 3) — returns ‘123’
Example 2: Extracting From the End
If you need the last four characters (e.g., a checksum):
plsql
CopyEdit
SELECT SUBSTR(raw_code, -4) — returns ‘0DEF’
Negative start positions let you grab suffixes without calculating length manually.
Example 3: Parsing Delimited Tokens
Suppose you have semi-colon delimited tags in a column:
sql
CopyEdit
‘red;green;blue;yellow’
To extract the second token (“green”), you can combine INSTR with SUBSTR:
plsql
CopyEdit
v_str := ‘red;green;blue;yellow’;
v_start := INSTR(v_str, ‘;’) + 1; — after first ‘;’
v_end := INSTR(v_str, ‘;’, 1, 2) – v_start; — up to second ‘;’
v_token := SUBSTR(v_str, v_start, v_end);
— v_token = ‘green’
Wrapping this logic in a function makes it reusable across your application.
Example 4: Dynamic Length Extraction
In a student records table, you want the first three letters of each city:
sql
CopyEdit
SELECT student_id,
SUBSTR(city, 1, 3) AS city_prefix
FROM students;
If you omit the length, you get all characters from the start position:
sql
CopyEdit
SUBSTR(city, 1) — same as city itself
SUBSTR(city, 5) — from the fifth character to end
Putting It All Together in a Procedure
Here’s a PL/SQL procedure that extracts multiple substrings in one go:
plsql
CopyEdit
CREATE OR REPLACE PROCEDURE parse_code(p_code IN VARCHAR2) IS
v_category VARCHAR2(3);
v_batch VARCHAR2(3);
v_checksum VARCHAR2(4);
v_token VARCHAR2(50);
BEGIN
v_category := SUBSTR(p_code, 1, 3);
v_batch := SUBSTR(p_code, 4, 3);
v_checksum := SUBSTR(p_code, -4);
DBMS_OUTPUT.PUT_LINE(‘Category : ‘ || v_category);
DBMS_OUTPUT.PUT_LINE(‘Batch : ‘ || v_batch);
DBMS_OUTPUT.PUT_LINE(‘Checksum : ‘ || v_checksum);
— Example: parse first tag in semi-colon string
IF INSTR(p_code, ‘;’) > 0 THEN
v_token := SUBSTR(p_code, 1, INSTR(p_code, ‘;’) – 1);
DBMS_OUTPUT.PUT_LINE(‘First Token: ‘ || v_token);
END IF;
END;
Call it with:
plsql
CopyEdit
BEGIN
parse_code(‘ABC123XYZ456DEF;alpha;beta’);
END;
Conclusion
Mastering SUBSTR in PL/SQL unlocks a host of string-manipulation strategies, from fixed-width parsing to dynamic token extraction. By combining SUBSTR with other built-in functions like INSTR, you can handle complex transformations without resorting to external code libraries. Incorporate these patterns into your everyday toolkit, and you’ll write cleaner, more efficient PL/SQL, and solve substring challenges with confidence.
