3 Replies Latest reply: Dec 29, 2012 7:38 AM by Frank Kulash RSS

    Problem with regexp_substr if string having null values between the commas.

    RamaKrishna.CH
      Hi all,

      My DB version is 10.1.0.5.0
      I want extract the values from the string using below query but i am unable to bring the correct result.
      WITH t  AS ( select '123,1,3,22' col FROM DUAL
              UNION ALL
              SELECT '123,,2,1' FROM DUAL
              UNION ALL
              SELECT '5,1,2,,' FROM DUAL
              ) 
      SELECT regexp_substr(t.col,'[^,]*',1,1)
            ,regexp_substr(t.col,'[^,]*',1,2)
            ,regexp_substr(t.col,'[^,]*',1,3)
            ,regexp_substr(t.col,'[^,]*',1,4) FROM DUAL,t   ;
      My required result like below.
      123     1      3      22
      123            2       1
      5       1      2        
      Please help me to get required result using regular expressions.
      Thanks,Ram
        • 1. Re: Problem with regexp_substr if string having null values between the commas.
          Manguilibe KAO
          Hi,

          Here's a way to do it:
          WITH t  AS ( select '123,1,3,22' col FROM DUAL
                  UNION ALL
                  SELECT '123,,2,1' FROM DUAL
                  UNION ALL
                  SELECT '5,1,2,,' FROM DUAL
                  ), a
                  as
          (SELECT t.col str, instr(t.col,',',1,1) pos1,
               instr(t.col,',',1,2) pos2,
                instr(t.col,',',1,3) pos3
                 FROM t)
          select substr(t.col,1, pos1 - 1) part1, substr(t.col,pos1 + 1,pos2 - pos1 -1) part2, substr(t.col, pos2 + 1, pos3 - pos2 -1) part3, rtrim(substr(t.col, pos3 + 1),',') part4
          from a,t
          where a.str= t.col;
          
          
          
          PART1      PART2      PART3      PART4    
          ---------- ---------- ---------- ----------
          123        1          3          22         
          123                   2          1          
          5          1          2                     
          If you want absolutely to use regexp to do it, you can just replace instr with regexp_instr:
          WITH t  AS ( select '123,1,3,22' col FROM DUAL
                  UNION ALL
                  SELECT '123,,2,1' FROM DUAL
                  UNION ALL
                  SELECT '5,1,2,,' FROM DUAL
                  ), a
                  as
          (SELECT t.col str,regexp_instr(t.col,',',1,1) pos1,
               regexp_instr(t.col,',',1,2) pos2,
                regexp_instr(t.col,',',1,3) pos3
                 FROM t)
          select substr(t.col,1, pos1 - 1) part1, substr(t.col,pos1 + 1,pos2 - pos1 -1) part2, substr(t.col, pos2 + 1, pos3 - pos2 -1) part3, rtrim(substr(t.col, pos3 + 1),',') part4
          from a,t
          where a.str= t.col;
          
          
          
          PART1      PART2      PART3      PART4    
          ---------- ---------- ---------- ----------
          123        1          3          22         
          123                   2          1          
          5          1          2       
          But there's no need to use regexp in your problem.


          Hope this helps.
          • 2. Re: Problem with regexp_substr if string having null values between the commas.
            Frank Kulash
            Hi,

            In Oracle 10, you can do it by combining REGEXP_SUBSTR with something else, such as RTRIM:
            SELECT     RTRIM (REGEXP_SUBSTR (col, '[^,]*(,|$)', 1, 1), ',')     AS part_1
            ,     RTRIM (REGEXP_SUBSTR (col, '[^,]*(,|$)', 1, 2), ',')     AS part_2
            ,     RTRIM (REGEXP_SUBSTR (col, '[^,]*(,|$)', 1, 3), ',')     AS part_3
            ,     RTRIM (REGEXP_SUBSTR (col, '[^,]*(,|$)', 1, 4), ',')     AS part_4
            FROM     t
            ;
            Starting in Oracle 11, REGEXP_SUBSTR alone can do it.

            But look at the first reply; regular expressions will probably be less efficient than less powerful functions, especially when all strings have the same number of delimiters.

            Edited by: Frank Kulash on Dec 29, 2012 8:36 AM
            • 3. Re: Problem with regexp_substr if string having null values between the commas.
              RamaKrishna.CH
              Frank,
              Your query works for me .
              In my scenario delimiters always same so i will do this as per first reply.

              Thank you.