AP 24 - Use LIKE instead of REGEX where LIKE is possible
Severity
WarningType
Performance
Problem
The regular expression engine is much more powerful than the LIKE operator when it comes to pattern matching. However, the regular expression engine requires significantly more resources than LIKE. Whenever you use a regular expression in a filter (WHERE clause), you should carefully assess whether a particular regular expression pattern has its LIKE equivalent or if available LIKE functionality is enough to meet your specific filtering requirement.
SELECT *
FROM tableX
WHERE col1 RLIKE 'DOMAIN.+END'
SELECT *
FROM tableY
WHERE REGEXP_LIKE(col2
,'DOMAIN.{3}$')
Solution
We have rewritten the queries using the regular expression with LIKE
SELECT *
FROM tableX
WHERE col1 LIKE 'DOMAIN%END%']
SELECT *
FROM tableY
WHERE col2 LIKE '%DOMAIN___'
Legitimate use of the antipattern
The following regular expression can not be rewritten with LIKE
SELECT *
FROM tableX
WHERE col1 RLIKE 'DOMAIN\s+(alpha|beta)[0-9A-z]_?END'