AP 24 - Use LIKE instead of REGEX where LIKE is possible

Severity

Type

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'