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? — Hauke
– Hauke, Commented Apr 27, 2018 at 6:53

krokodilko · Accepted Answer · 2018-04-27 03:55:56Z

4

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)

answered Apr 27, 2018 at 3:55

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mocking Over a year ago

This reply was actually better than I expected. Thanks!

Gary_W · Accepted Answer · 2018-04-27 21:02:05Z

2

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

answered Apr 27, 2018 at 21:02

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

1 Comment

Mocking Over a year ago

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.

Collectives™ on Stack Overflow

PL/SQL - Split string into an associative array

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related