This discussion is archived
3 Replies Latest reply: Dec 29, 2012 5:38 AM by Frank Kulash RSS

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

RamaKrishna.CH Explorer
Currently Being Moderated
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.
    ManguilibeKAO Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Frank,
    Your query works for me .
    In my scenario delimiters always same so i will do this as per first reply.

    Thank you.

Legend

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