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.

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.