Examples using Oracle Regular Expression Implementation

Comments
-
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.
BobB0 -
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".0