Splitting String Using Delimiter in Oracle

Let us suppose we have a string like 'GO 4 EXPERT|99999|20130101|20131231' and we have a requirement to split the string based on delimiter.

And the required output is as below

Code:

GO 4 EXPERT
99999
20130101
20131231

We need to write a query to which will split the string.

Now we will see different queries which will solve our problem

If we know the combinations of values or string's in a string then we need to use below query

Code:

select 
          regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, rn) as splited_string
     from dual
     cross
     join (select rownum as rn
             from dual
          connect by level <= 4)

If we dn't know the combinations of values or string's in a string then we need to use below query

Code:

select 
          regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, rn) as splited_string
     from dual
     cross
     join (select rownum as rn
             from (select length (regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+')) + 1 as mx
                     from dual
                  )
          connect by level <= mx)

Here pipe is used as delimiter.

Now we will try to understand how the queues will work.

Before going into the query we need to understand below functions first

1. regexp_substr
2. regexp_replace
3. connect by level

1. regexp_substr :- Before explaining about regexp_substr i guess readers know about substr function. regexp_substr comes under regular expression. This function will help you out in searching a string for a regular expression pattern.

Take an example from above query

Code:

select regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, 1) as splited_string from dual

will return

Code:

GO 4 EXPERT

In above expression

"^" marks the start of the string and

The "+" sign stands for one or more occurrences

2. regexp_replace :- This function will help you out in replacing a string for a regular expression pattern.

Take an example from above query

Code:

 select regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+') as replaced_string from dual

will return

Code:

 |||

In the query we have not given any string for replacement so it replaces with null.

By looking into below example we will get a clear understanding

Code:

 select regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+','ABC') as replaced_string from dual

will return.

Code:

 ABC|ABC|ABC|ABC

3. connect by :- connect by is a condition which will tell the relationship between parent node and child node.

Now we will see how first query works.

Code:

select 
          regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, rn) as splited_string
     from dual
     cross
     join (select rownum as rn
             from dual
          connect by level <= 4)
          

The inner query

Code:

select rownum as rn
             from dual
          connect by level <= 4

will give below output

Code:

 RN
   
   1
   2
   3
   4

In the query level is the Pseudo column. 4 represent no. of combination in the string. This inner query is used for getting the value for regexp_substr function for the position.
The connect by level is used for looping.

Now the regexp_substr function will get values by cross joining as below

Code:

  regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, 1)

Code:

regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, 2)

Code:

 regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, 3)

Code:

regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, 4)

Now one can check the result of above function output by selecting from dual.

The inner query mainly serves as an loop which provides values to outer query.

Now we will see how second query works.

Code:

 select 
             regexp_substr ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+', 1, rn) as splited_string
        from dual
        cross
        join (select rownum as rn
                from (select length (regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+')) + 1 as mx
                        from dual
                     )
          connect by level <= mx)

Previously we have seen that 4 is used in connect by level condition. If we don't know the combination of a string the we need to know the no. of combination.

To know the no. of combination in the string we have used below query

Code:

 select length (regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+')) + 1 as mx
                        from dual

As we have already saw the output of regexp_replace ('GO 4 EXPERT|99999|20130101|20131231', '[^|]+') above. It will give output as three pipes. So taking length of that will give the no. of pipes in the string. But we want to know the combination. If we see the source string , we can see that two string's are divided with pipe delimiter.

So no. of combination is (No. of pipe delimiter + 1). Now it is equal to 4 for the present string. Rest of the code works as first query.