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.

How to Split a String Based on Delimiter in PL/SQL?

When working with delimited strings in PL/SQL, developers often need to split the string to access individual items. This can be achieved using different approaches, such as using regular expressions, built-in functions, or custom PL/SQL code. Below is the method that helps us to efficiently split a delimited string to access individual items.

1. Using SUBSTR and INSTR Functions
2. Using REGEXP_SUBSTR Function
3. Creating a User-Defined Function (UDF) for Delimited String Splitting

Let’s set up an environment:

To understand How to split a delimited string to access individual items in PL/SQL, we need a table on which we will perform various operations and queries. Here we will consider a table called products which contains product_id and product_list  as Columns.

1. Using SUBSTR and INSTR Functions

One common approach to splitting delimited strings in PL/SQL involves using the SUBSTR and INSTR functions to extract individual items iteratively based on the delimiter position.

The basic syntax for splitting a delimited string using SUBSTR and INSTR functions is as follows:

DECLARE
    delimited_string VARCHAR2(4000) := 'item1,item2,item3';
    delimiter CHAR := ',';
    start_index NUMBER := 1;
    end_index NUMBER;
    item VARCHAR2(255);
BEGIN
    LOOP
        end_index := INSTR(delimited_string, delimiter, start_index);
        IF end_index = 0 THEN
            item := SUBSTR(delimited_string, start_index);
            EXIT;
        END IF;
        
        item := SUBSTR(delimited_string, start_index, end_index - start_index);
        -- Process item here
        -- Example: DBMS_OUTPUT.PUT_LINE(item);
        
        start_index := end_index + 1;
    END LOOP;
END;

Example:

DECLARE
    delimiter CHAR := ',';
    start_index NUMBER := 1;
    end_index NUMBER;
    item VARCHAR2(255);
BEGIN
    FOR prod_rec IN (SELECT product_id, product_list FROM your_table_name_here) LOOP
        start_index := 1; -- Reset start_index for each product_list
        
        LOOP
            end_index := INSTR(prod_rec.product_list, delimiter, start_index);
            IF end_index = 0 THEN
                item := SUBSTR(prod_rec.product_list, start_index);
                EXIT;
            END IF;
            
            item := SUBSTR(prod_rec.product_list, start_index, end_index - start_index);
            DBMS_OUTPUT.PUT_LINE('Product ID: ' || prod_rec.product_id || ', Item: ' || item);
            
            start_index := end_index + 1;
        END LOOP;
    END LOOP;
END;

Output:

Output

Explanation: In the above query we have iterates over each row in a table containing product_id and product_list columns. It splits the product_list string using a comma as a delimiter and prints each item along with its corresponding product_id.

INSTR function is used to find the delimiter position, and SUBSTR is used to extract the item. The start_index is reset for each product_list string.

2. Using REGEXP_SUBSTR Function

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:

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:

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.

Conclusion

Overall, we explored multiple methods for splitting delimited strings in PL/SQL using a sample table named “products.” With the help of SUBSTR and INSTR functions, REGEXP_SUBSTR function and user-defined functions (UDFs) developers can efficiently parse delimited strings and access individual items for further processing. Whether extracting values from CSV files or handling user input, mastering these delimited string parsing techniques enhances code flexibility and performance in PL/SQL development.