How to use REGEXP_SUBSTR Function In SQL
Another approach to splitting delimited strings in PL/SQL involves utilizing the REGEXP_SUBSTR function, which supports regular expressions for pattern matching and extraction.
The syntax for splitting a delimited string using REGEXP_SUBSTR function is as follows:
DECLARE
delimited_string VARCHAR2(4000) := 'item1,item2,item3';
delimiter CHAR := ',';
item VARCHAR2(255);
BEGIN
FOR i IN 1..REGEXP_COUNT(delimited_string, delimiter) + 1 LOOP
item := REGEXP_SUBSTR(delimited_string, '[^'|| delimiter ||']+', 1, i);
-- Process item here
-- Example: DBMS_OUTPUT.PUT_LINE(item);
END LOOP;
END;
Example:
DECLARE
CURSOR c_product IS
SELECT product_id, product_list FROM your_table_name;
delimiter CHAR := ',';
item VARCHAR2(255);
BEGIN
FOR rec IN c_product LOOP
DBMS_OUTPUT.PUT_LINE('Product ID: ' || rec.product_id);
FOR i IN 1..REGEXP_COUNT(rec.product_list, delimiter) + 1 LOOP
item := REGEXP_SUBSTR(rec.product_list, '[^'|| delimiter ||']+', 1, i);
DBMS_OUTPUT.PUT_LINE('Item: ' || item);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------------------------');
END LOOP;
END;
Output:
Explanation: In the above query, we have uses a cursor to fetch product_id
and product_list
from a table. It then iterates over each row, splitting the product_list
string using a regular expression to find items separated by a delimiter.
Each item is printed along with its corresponding product_id
, and a separator is printed between each product for clarity.
3. Creating a User-Defined Function (UDF) for Delimited String Splitting
Developers can create a user-defined function (UDF) to encapsulate the logic for splitting delimited strings, offering reusability and modularity in PL/SQL code.
The syntax for creating a UDF to split delimited strings is as follows:
CREATE OR REPLACE FUNCTION split_string(
p_string VARCHAR2,
p_delimiter VARCHAR2
) RETURN SYS.ODCIVARCHAR2LIST
PIPELINED IS
v_start_index NUMBER := 1;
v_end_index NUMBER;
BEGIN
WHILE v_start_index <= LENGTH(p_string) LOOP
v_end_index := INSTR(p_string, p_delimiter, v_start_index);
IF v_end_index = 0 THEN
PIPE ROW (SUBSTR(p_string, v_start_index));
RETURN;
END IF;
PIPE ROW (SUBSTR(p_string, v_start_index, v_end_index - v_start_index));
v_start_index := v_end_index + 1;
END LOOP;
END split_string;
Example:
Suppose we create a UDF named “split_string” to split delimited strings:
CREATE OR REPLACE FUNCTION split_string(
p_string VARCHAR2,
p_delimiter VARCHAR2
) RETURN SYS.ODCIVARCHAR2LIST
PIPELINED IS
v_start_index NUMBER := 1;
v_end_index NUMBER;
BEGIN
WHILE v_start_index <= LENGTH(p_string) LOOP
v_end_index := INSTR(p_string, p_delimiter, v_start_index);
IF v_end_index = 0 THEN
PIPE ROW (SUBSTR(p_string, v_start_index));
RETURN;
END IF;
PIPE ROW (SUBSTR(p_string, v_start_index, v_end_index - v_start_index));
v_start_index := v_end_index + 1;
END LOOP;
END split_string;
Output:
Explanation: In the above query, we have create a function named split_string
that takes a string (p_string
) and a delimiter (p_delimiter
) as input and returns a collection (SYS.ODCIVARCHAR2LIST
) of substrings obtained by splitting the input string based on the delimiter.
How to split a delimited string to access individual items in PL/SQL?
In PL/SQL, dealing with delimited strings is a common task, especially when handling data from external sources or processing user inputs. One challenge developers face is how to efficiently split a delimited string to access individual items. In this article, we will explore various approaches to splitting delimited strings in PL/SQL and accessing their items.