PL/SQL - Split string into an associative array

Question

In plsql is there a way to split a string into an associative array?

Sample string: 'test1:First string, test2: Second string, test3: Third string'

INTO

TYPE as_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
a_array as_array;

dbms_output.put_line(a_array('test1')); // Output 'First string' 
dbms_output.put_line(a_array('test2')); // Output 'Second string'
dbms_output.put_line(a_array('test3')); // Output 'Third string'

The format of the string does not matter for my purposes. It could be 'test1-First string; test2-Second string; test3-Third string'. I could do this with a very large function manually splitting by commas first and then splitting each of those but I'm wondering if there is something built in to the language.

Like I said, I am not looking to do it through a large function (especially using substr and making it look messy). I am looking for something that does my task simpler.

Possible duplicate of [Is there a function to split a string in PL/SQL?](https://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-pl-sql) — Hauke, Apr 27 '18 at 06:53

score 4 · Accepted Answer · answered Apr 27 '18 at 03:55

There is no built in function for such a requirement.
But you can easily build a query like below to parse these strings:

SELECT y.* 
FROM (
    select trim(regexp_substr(str,'[^,]+', 1, level)) as str1
    from ( 
       SELECT 'test1:First string, test2: Second string, test3: Third string' as Str 
       FROM dual 
    )
    connect by regexp_substr(str, '[^,]+', 1, level) is not null
) x
CROSS APPLY(
    select trim(regexp_substr(str1,'[^:]+', 1, 1)) as key,
           trim(regexp_substr(str1,'[^:]+', 1, 2)) as value
    from dual
) y

KEY    VALUE         
------ --------------
test1  First string  
test2  Second string 
test3  Third string

Then you may use this query in your function and pass it's result to the array.
I leave this exercise for you, I believe you can manage it (tip: use Oracle's bulk collect feature)

This reply was actually better than I expected. Thanks! – Mocking Apr 27 '18 at 19:23 — Mocking, Apr 27 '18 at 19:23

score 2 · Answer 2 · answered Apr 27 '18 at 21:02

This method handles NULL list elements if you need to still show that element 2 is NULL for example. Note the second element is NULL:

-- Original data with multiple delimiters and a NULL element for testing.
with orig_data(str) as (
  select 'test1:First string,, test3: Third string' from dual 
),
--Split on first delimiter (comma)
Parsed_data(rec) as (
  select regexp_substr(str, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
  from orig_data
  where str is not null
  CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1 
)
-- For testing-shows records based on 1st level delimiter
--select rec from parsed_data;

-- Split the record into columns
select trim(regexp_replace(rec, '^(.*):.*', '\1')) key,
       trim(regexp_replace(rec, '^.*:(.*)', '\1')) value
from Parsed_data;

Watch out for the regex form of [^,]+ for parsing delimited strings, it fails on NULL elements. More Information

Sorry, I think I wasn't that clear but the numbers don't really have a meaning or an order. I think this would be useful if it works for a key but a blank value. I think for other applications though this would be really cool. — Mocking, Apr 27 '18 at 21:07

PL/SQL - Split string into an associative array

2 Answers2

Linked