Dealing with string manipulation in PL/SQL isn’t hard, but it can be more work than it’s worth. Converting lists of values coming from HTML inside a single text item, separating lists based on multilple types of separators or even just the first N elements can require you to make an algorithm for something that kind of trivial.
For that reason I would like to show you the APEX_STRING.SPLIT function that should take care of most of you issues. Let’s take a look at the following example:
DECLARE
l_text VARCHAR2(100) := 'apple,banana,melon,orange';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;
This is a basic example that receives 2 parameters, one which is the varchar2 that we want to split and the other is the separator. Easy
Now lets take a look on this example where the delimiters are different between themselves.
DECLARE
l_text VARCHAR2(100) := 'apple;banana,melon:grapes,lemon';
l_delimiter VARCHAR2(10) := '[;,:]';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;
As you may have noticed, the separator is provided as a regular expression, which means we get a lot of flexibility when working this way.
For this last example, we’ll be applying a delimiter.
DECLARE
l_text VARCHAR2(100) := 'apple,banana,cherry,date';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter, 2);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;
This is quite easy as well; it simply stops our breaking of the varchar after Nth iterations. where the Nth item in the array will have all remaining values.
Do you have CLOBs or Numbers?
There are two other functions I think are worth mentioning, and the have nearly the same signature, which could help you with numbers and values larger than varchar2.
- SPLIT_CLOBS: take is a CLOB as the first parameter, same separation and limit rules. Return an apex_t_clob
- SPLIT_NUMBERS: Doesn’t have the limit parameter and return an apex_t_number
Conclusion
That’s about it, with this package you can do a lot more and if you are working with APEX I highly recommend you taking a look at it’s documentation at https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/APEX_STRING.html#GUID-CAFD987C-7382-4F0F-8CB9-1D3BD05F054A