CODEWITHSUNDEEP
sqldatabaseoracle-database
minerals
minerals

Reputation: 1315

SQL regexp with numbers and % character

I need to match the following string "50% off" in Oracle db. I've created an SQL query. 

  select *
     from table1
     where column1 like '%[0-9]+\% off%' ESCAPE '\';

This query however does not provide any results. Moreover how do I match "50% off" AND "$50 off" with one query? 

'%[0-9]+\%? off%' ESCAPE '\'

The one above does not work either :(

The only query I got working was the following:

select *
         from table1
         where column1 like '%\% off%' ESCAPE '\';

Upvotes: 1

Views: 157

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

The LIKE operator in Oracle only accepts single-character (_) and multi-character (%) wildcards and not regular expression.

Instead use the REGEXP_LIKE condition:

SQL>  WITH DATA AS (
  2   SELECT '50% off' txt FROM dual
  3   UNION ALL SELECT '$50 off' txt FROM dual)
  4   SELECT *
  5     FROM DATA
  6    WHERE regexp_like(txt, '([[:digit:]]+%)|(\$[[:digit:]]+) off');

TXT
-------
50% off
$50 off

Upvotes: 4

Related Questions