SQL Language (MOSC)

MOSC Banner

Examples using Oracle Regular Expression Implementation

GregV
GregV Posts: 4,064 Gold Crown

Comments

  • BobBeattie
    BobBeattie Posts: 4,512 Silver Crown
    Hi Gregory
    A very useful note.
     
    I don't know if you know it, but there is a bug in the implementation of regular expressions. I raised an SR about it several years ago but nothing came of it.
     
    The problem is that if you use a string in more than one context, the context is ignored for the second and sunsequent use. It applies to all the functions, in 10G and 11gR1 (I haven't managed to test in 11gR2 yet).
     
    In this example, the first regexp_substr's are the ones with problems and the other two possible workrounds.
     
    I am using the string "ab". In the first query, the first context is as a matching list "[ab]" and the second as a simple string. So "[ab]ab" should match "aab" or "bab". However the second use of "ab" is treated as a matching list as well, so it matches "aa", "ab", "ba" or "bb".
     
    In the second query it is the other way round so should match "aba" and "abb" but it matches "abab".

     
    SQL> create table test (col1 varchar2(10))
    Table created.
    SQL> insert into test values ('abab')
    1 row created.
    SQL> insert into test values ('aaba')
    1 row created.
    SQL> insert into test values ('baba')
    1 row created.
    SQL> insert into test values ('bbab')
    1 row created.
     
    SQL> select col1, regexp_substr(col1,'[ab]ab'),
                      regexp_substr(col1,'[abc]ab'),
                      regexp_substr(col1,'(a|b)ab')  from test
     
    COL1       REGEXP_SUB REGEXP_SUB REGEXP_SUB
    ---------- ---------- ---------- ----------
    abab       ab         bab        bab      
    aaba       aa         aab        aab      
    baba       ba         bab        bab      
    bbab       bb         bab        bab      
     

    4 rows selected.
    SQL> select col1, regexp_substr(col1,'ab[ab]'),
                      regexp_substr(col1,'ab[abc]'),
                      regexp_substr(col1,'ab(a|b)')  from test
     
    COL1       REGEXP_SUB REGEXP_SUB REGEXP_SUB
    ---------- ---------- ---------- ----------
    abab       abab       aba        aba      
    aaba                  aba        aba      
    baba                  aba        aba      
    bbab                                      
     

    4 rows selected.
    SQL> drop table test
    Table dropped.

     
    It is a fairly trivial test, but does show that extreme care has to be taken when using Oracle's implementation of regular expressions. You may want to add a bit to your note.
    BobB 

  • Nip-Oracle
    Nip-Oracle Posts: 757 Gold Badge
    First time, I found a document with the examples of SQL in traditional ways and how we can implement the same with Regular Expressions. This kind of explaination opens up the mind where we can think to replace traditional ways with powerful "Regular Expressions". 

Welcome!

It looks like you're new here. Sign in or register to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center