Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just to add to those two articles on the subject, here is a third one combining the first two articles.

Recently I was asked for help in a comment on how to go about and split up a string like the following

      'ABC/FDF,RET/YRT,UYT/ERT'
   

The expected outcome would be

      ABC
      FDF
      RET
      YRT
      UYT
      ERT
   

As you can see the input string consists of two different delimiters, namely a comma and a forward slash (/).
To split this string up, you will need both techniques from the other articles.

Let's start with a variable containing the input string.

      SQL> var input varchar2(150)
      SQL> 
      SQL> exec :input := 'ABC/FDF,RET/YRT,UYT/ERT'

      PL/SQL procedure successfully completed.
   

The first step is to split the string up using the first method, split up the string using the comma as a delimiter.

SQL> select regexp_substr (:input, '[^,]+',1, rownum) str
  2    from dual
  3   connect by level <= regexp_count (:input, '[^,]+')
  4  ;

STR
-------
ABC/FDF
RET/YRT
UYT/ERT
   

This will leave us with three records each consisting of a string that needs further splitting up, but this time with the forward slash as the delimiter.

Using these rows as the input in the next phase, use the technique described in the second article.
By introducing Subquery Factoring (lines 1-5), create a named query "commas"

   SQL> with commas
     2  as
     3  (select regexp_substr (:input, '[^,]+',1, rownum) str
     4    from dual
     5   connect by level <= regexp_count (:input, '[^,]+'))
     6   select regexp_substr (str, '[^\/]+', 1, rn) split
     7   from commas
     8   cross
     9   join (select rownum rn
    10       from (select max (regexp_count(rtrim (str, '/')||'/', '\/')) mx
    11        from commas
    12     )
    13    connect by level <= mx
    14    )
    15   where regexp_substr (str, '[^\/]+', 1, rn) is not null
    16   ;

   SPLIT
   ----------------------------------
   ABC
   FDF
   RET
   YRT
   UYT
   ERT

The forward slash has special meaning with regular expressions it needs to be escaped using a backslash.
You can see this on lines 6, 10, and 15.
What is interesting, or at least I find interesting, is the use of the RTRIM on line 10.
Each value per line is not completely delimited by the forward slashes, the trailing one is missing. Just to concatenate one to each line would be to easy, what if there is a trailing slash?
The RTRIM removes the trailing slash and concatenates one at the end, making sure that the string is split up at the right place.

Links

• Part One
• Part Two

Splitting a comma delimited string the RegExp way, Part Two

Over two years ago I wrote about a way to split a comma delimited string using Regular Expresssions. Just a little while ago someone asked how to split it when you have more records involved than just one (as I used in my example).

Splitting a comma delimited string the RegExp way

This is one of those recurring questions on the Oracle Forums of OTN.
How to split a comma delimited string? Of course there are several options how to tackle this problem. One of the most elegant ones, at least I think so, uses a regular expression.

Let's just look at an example

SQL> with test as  
  2  (select 'ABC,DEF,GHI,JKL,MNO' str from dual  
  3  )  
  4  select regexp_substr (str, '[^,]+', 1, rownum) split  
  5    from test  
  6  connect by level <= length (regexp_replace (str, '[^,]+'))  + 1
  7  /  

SPLIT
---------------------------------------------------------------------
ABC
DEF
GHI
JKL
MNO

The first part creates some test data using the WITH clause (aka Subquery Factoring). The actual query with the regular expression starts on line 4.
The expression is

regexp_substr (str, '[^,]+', 1, rownum)

The meaning of "[^,]+" in normal English:

Give me one or more characters which are not in the list

The list consists of all characters between the square brackets. Here the "^" (circumflex) indicates "except" or "not in". The "+" means: one or more times.

The arguments of the REGEXP_SUBSTR determine which part of the string to subtract.
The third argument to the REGEXP_SUBSTR functions tells it where to start with the regular expression. The last argument means which occurence to match.

REGEXP_SUBSTR
Multilingual Regular Expression Syntax