Search code examples
sqloracle-databasecoldfusion

Using REGXP_LIKE within queryExecute()

I'm looking to modify an existing query to use REGEXP_LIKE but falling foul of some syntax I'm not understanding properly. We've currently a CF query into an Oracle DB using the following:

result = QueryExecute("
  SELECT paramOne, paramTwo FROM someTable WHERE fieldOne = :PUBLISHER
", {PUBLISHER=publisherId}, {datasource="someDB"});

which works. However, I want to modify the underlying query to be:

result = QueryExecute("
  SELECT paramOne, paramTwo FROM someTable WHERE REGEXP_LIKE(fieldOne, '(^|,)(:PUBLISHER)($|,)', 'i')
", {PUBLISHER=publisherId}, {datasource="someDB"});

but it's not delivering the expected results. A few things I've noted as I try to debug...

  • The underlying query(without using a variable) works and has been verified in Oracle SQL

  • If I go to the source code and replace :PUBLISHER with a 'hard coded' value the things work as expected.

  • I've tried escaping the ':' but that's not the answer.

I fell there's something I'm not understanding about passing variables into a REGEX expression within queryExecute(), so would appreciate any thoughts.

Any input gratefully received, Phil

Solution 
  • SQL> 
with t (fieldOne) as  (
  select 'abc, def' from dual union all
  select 'def cba' from dual union all
  select ':publisher' from dual
)
select * from t where REGEXP_LIKE(fieldOne, '(^|,)(:PUBLISHER)($|,)', 'i');

FIELDONE  
----------
:publisher

SQL> 
with t (fieldOne) as  (
  select 'abc, def' from dual union all
  select 'def cba' from dual union all
  select ':publisher' from dual
)
select * from t where REGEXP_LIKE(fieldOne, '(^|,)(' || :PUBLISHER || ')($|,)', 'i');

FIELDONE  
----------
abc, def