I don’t use REGEXP much but ran across this interesting issue just recently.
Apparently REGEXP_LIKE doesn’t like parentheses in search strings, the LIKE operator doesn’t have an issue with parentheses.
The full code will be at the bottom of this if you’d like to try it yourself. I did run this on a super-cool Autonomous Database, version 21.3 so it’s not an old version thing.
What I do in the test is compare a column form one table to another, this is simulating the problem that I ran into recently. In the string are some parentheses. And you’ll notice that when these values are in the first string in the REGEXP_LIKE operation, it fails.
The Right-side data will be this (Table called regextest01, yea I was not very creative with my table name for this):
'ABCD(XXX)101', 'ABCD(xxx', 'ABCD)xxxx'
Then the Left-side will be these values one at a time (Table called regextest02, yea I was not very creative with my table names for this example):
'ABCD', 'ABCD(xxx)', 'ABCD(', 'ABCD)'
The first one as you expect works fine for both the REGEXP_LIKE and the LIKE, returning all three matching rows.
The second one there is no error for REGEXP_LIKE however it returns no rows. The LIKE returns the one you would expect with the value in the first table of 'ABCD(XXX)101'. Hum that’s odd, it appears that the parentheses inside the string for REGEXP_LIKE are not treated as part of the string but as some sort of parameter or something. Very odd to me at least.
And the last two error out with:
Error report -
ORA-12725: unmatched parentheses in regular expression
For the REGEXP_LIKE, but work just fine with the LIKE.
Interesting. So, for me at least, just one more reason to not use REGEXP_LIKE. Clearly the parentheses are a problem for REGEXP_LIKE and not an issue for LIKE.
In the script below each comparison is done one at a time. Just to make it easier to see how each comparison works. Full code here so you can run this and see it live and in color, well maybe not much color:
/* test code of REGEXP_LIKE testing for parentheses */
CREATE TABLE regextest01 (
id NUMBER,
string01 VARCHAR2(20)
);
CREATE TABLE regextest02 (
id NUMBER,
string02 VARCHAR2(20)
);
insert into regextest01 values (11, 'ABCD(XXX)101');
insert into regextest01 values (22, 'ABCD(xxx');
insert into regextest01 values (33, 'ABCD)xxxx');
insert into regextest02 values (11, 'ABCD');
prompt Value in regextest02: ABCD
prompt Using REXEXP_LIKE:
select * from regextest01 rt01, regextest02 rt02
where regexp_like(rt01.string01, rt02.string02, 'i');
prompt Using LIKE:
select * from regextest01 rt01, regextest02 rt02
where upper(rt01.string01) like '%'||upper(rt02.string02)||'%';
/* Change the comparison string to have an opening and closing parentheses in it*/
delete from regextest02;
insert into regextest02 values (11, 'ABCD(xxx)');
prompt Value in regextest02: ABCD(xxx)
prompt Using REXEXP_LIKE:
select * from regextest01 rt01, regextest02 rt02
where regexp_like(rt01.string01, rt02.string02, 'i');
prompt Using LIKE:
select * from regextest01 rt01, regextest02 rt02
where upper(rt01.string01) like '%'||upper(rt02.string02)||'%';
/* Change the comparison string to have an opening parentheses in it*/
delete from regextest02;
insert into regextest02 values (11, 'ABCD(');
prompt Value in regextest02: ABCD(
prompt Using REXEXP_LIKE:
select * from regextest01 rt01, regextest02 rt02
where regexp_like(rt01.string01, rt02.string02, 'i');
prompt Using LIKE:
select * from regextest01 rt01, regextest02 rt02
where upper(rt01.string01) like '%'||upper(rt02.string02)||'%';
/* Change the comparison string to have an closing parentheses in it*/
delete from regextest02;
insert into regextest02 values (11, 'ABCD)');
prompt Value in regextest02: ABCD)
prompt Using REXEXP_LIKE:
select * from regextest01 rt01, regextest02 rt02
where regexp_like(rt01.string01, rt02.string02, 'i');
prompt Using LIKE:
select * from regextest01 rt01, regextest02 rt02
where upper(rt01.string01) like '%'||upper(rt02.string02)||'%';
drop table regextest01 purge;
drop table regextest02 purge;
