1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Splitting String Using Delimiter in Oracle

Discussion in 'Oracle' started by bashamsc, Feb 12, 2013.

  1. bashamsc

    bashamsc New Member

    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.
     

Share This Page