7 Replies Latest reply: Jan 10, 2013 11:40 PM by 936666 RSS

    Split the String

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      Thanks