This discussion is archived
7 Replies Latest reply: Jan 10, 2013 9:40 PM by 936666 RSS

Split the String

936666 Newbie
Currently Being Moderated
how to split the input string

'A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@'

as
Col1 ,Col2,Col3
A,B,C
A,B,C
A,B,C
  • 1. Re: Split the String
    Solomon Yakobson Guru
    Currently Being Moderated
    with t as (
               select 'A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@' str from dual
              )
    select  regexp_substr(str,'[^:@]+',1,column_value * 3 + 1 ) col1,
            regexp_substr(str,'[^:@]+',1,column_value * 3 + 2 ) col2,
            regexp_substr(str,'[^:@]+',1,column_value * 3 + 3 ) col3
      from  t,
            table(
                  cast(
                       multiset(
                                select  level - 1
                                  from  dual
                                  connect by level <= length(str) - length(replace(str,'@'))
                               )
                       as sys.OdciNumberList
                      )
                 )
    /
    
    COL1                                 COL2                                 COL3
    ------------------------------------ ------------------------------------ ------
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    
    6 rows selected.
    
    SQL> 
    SY.
  • 2. Re: Split the String
    Rahul_India Journeyer
    Currently Being Moderated
    Solomon Yakobson wrote:
    with t as (
    select 'A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@' str from dual
    )
    select  regexp_substr(str,'[^:@]+',1,column_value * 3 + 1 ) col1,
    regexp_substr(str,'[^:@]+',1,column_value * 3 + 2 ) col2,
    regexp_substr(str,'[^:@]+',1,column_value * 3 + 3 ) col3
    from  t,
    table(
    cast(
    multiset(
    select  level - 1
    from  dual
    connect by level <= length(str) - length(replace(str,'@'))
    )
    as sys.OdciNumberList
    )
    )
    /
    
    COL1                                 COL2                                 COL3
    ------------------------------------ ------------------------------------ ------
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    A                                    B                                    C
    
    6 rows selected.
    
    SQL> 
    SY.
    Solomon you know any docs tutorial on regular expression with examples
  • 3. Re: Split the String
    Solomon Yakobson Guru
    Currently Being Moderated
    Well, regexp isn't Oracle's "invention", so you can read almost any regexp tutorial (obviously, Oracle supports just small subset of what regexp can do). Personally, I knew regexp from old C programming days, so I wasn't looking for "modern" tutorials. Sorry, can't help you with that.

    SY.
  • 4. Re: Split the String
    Rahul_India Journeyer
    Currently Being Moderated
    Solomon Yakobson wrote:
    Well, regexp isn't Oracle's "invention", so you can read almost any regexp tutorial (obviously, Oracle supports just small subset of what regexp can do). Personally, I knew regexp from old C programming days, so I wasn't looking for "modern" tutorials. Sorry, can't help you with that.

    SY.
    ok thanks will do.I struggle with regular expressions.Any tips?
  • 5. Re: Split the String
    Solomon Yakobson Guru
    Currently Being Moderated
    Regexp used in this case is quite simple. We want to grab substrings that do not have : or @, right, so [^:@]+ means any character but : or @ repeated one or more times.

    SY.
  • 6. Re: Split the String
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul India wrote:
    Solomon Yakobson wrote:
    Well, regexp isn't Oracle's "invention", so you can read almost any regexp tutorial (obviously, Oracle supports just small subset of what regexp can do). Personally, I knew regexp from old C programming days, so I wasn't looking for "modern" tutorials. Sorry, can't help you with that.

    SY.
    ok thanks will do.I struggle with regular expressions.Any tips?
    Perhaps look in the FAQ?

    {message:id=9360010}
  • 7. Re: Split the String
    936666 Newbie
    Currently Being Moderated
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points