MariaDB - Regular Expression

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.

MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas pke recursive patterns, look-ahead assertions, and more.

Review the use of standard REGEXP operator syntax given below −

SELECT column FROM table_name WHERE column REGEXP  [PATTERN] ;

REGEXP returns 1 for a pattern match or 0 in the absence of one.

An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibipty reasons.

The pattern compared can be a pteral string or something else such as a table column. In strings, it uses C escape syntax, so double any “” characters. REGEXP is also case-insensitive, with the exception of binary strings.

A table of possible patterns, which can be used are given below −

Review the pattern matching examples given below −

Products starting with “pr” −

SELECT name FROM product_tbl WHERE name REGEXP  ^pr ;

Products ending with “na” −

SELECT name FROM product_tbl WHERE name REGEXP  na$ ;

Products starting with a vowel −

SELECT name FROM product_tbl WHERE name REGEXP  ^[aeiou] ;