How to chunk a string in pl sql using regexp?

Question

I have a string as follows: ABCAPP9 Xore-Done-1. I want to chunk the string to get 4 elements separately at a given time in pl sql. Pls tell me the 4 different queries to get the following 4 results separately. Thanks

1. ABCAPP9
2. Xore
3. Done
4. 1

Answer 1

REGEXP_SUBSTR ('ABCAPP9 Xore-Done-1', '[^[:space:]-]+', 1, n)

will give you the n-th part. Change n with the number you want. Here are all:

    SELECT REGEXP_SUBSTR ('ABCAPP9 Xore-Done-1', '[^[:space:]-]+', 1, LEVEL)
      FROM DUAL
CONNECT BY REGEXP_SUBSTR ('ABCAPP9 Xore-Done-1', '[^[:space:]-]+', 1, LEVEL) IS NOT NULL

Answer 2

This should be a comment really to @Mottor but due to no formatting in comments I need to make it here.

A word of warning. As long as all elements of your string will be present and the delimiters can NEVER be next to each other you will be ok. However, the regex format of '[^<delimiter>]+' commonly used for parsing strings will not return the correct value if there is a NULL element in the list! See this post for proof: https://stackoverflow.com/a/31464699/2543416. To test in your example, remove the substring "Xore", leaving the space and hyphen next to each other:

SQL> SELECT REGEXP_SUBSTR ('ABCAPP9 -Done-1', '[^[:space:]-]+', 1, LEVEL)
         FROM DUAL
   CONNECT BY REGEXP_SUBSTR ('ABCAPP9 -Done-1', '[^[:space:]-]+', 1, LEVEL) IS NOT NULL;

REGEXP_SUBSTR('
---------------
ABCAPP9
Done
1

The 2nd element should be NULL, but "Done" is returned instead! Not good if the position is important.

Use this format instead to handle NULLs and return the correct string element in the correct position (shown here with "Xore" removed and thus a NULL returned in that position to prove it handles the NULL):

SQL> with tbl(str) as (
     select 'ABCAPP9 -Done-1' from dual
   )
   select regexp_substr(str, '(.*?)( |-|$)', 1, level, NULL, 1)
   from tbl
   connect by regexp_substr(str, '(.*?)( |-|$)', 1, level) is not null;

REGEXP_SUBSTR(S
---------------
ABCAPP9

Done
1

SQL>

I shudder to think of all the bad data being returned out there.

So user2153047, if you are still with me, for your need if you want the 3rd element (and handle the NULL) you would use:

SQL> select regexp_substr('ABCAPP9 -Done-1', '(.*?)( |-|$)', 1, 3, NULL, 1) "3rd"
       from dual;

3rd
----
Done