How to use SUBSTR and INSTR Functions In SQL
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:
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.
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.
Contact Us