like_to_rlike

NAME

like_to_rlike(): Convert a LIKE expression to an RLIKE (REGEXP) expression.

TYPE

Function

DESCRIPTION

This function modifies a LIKE expression into a compatible expression to work with RLIKE (REGEXP).

LIKE expressions use "_" for single character pattern mapping, and "%" for multiple characters (zero or more) pattern mapping.

Regular expressions use "." and ".*" instead. The routine translates to a matching regular expression pattern, while taking care to escape some (this is incomplete) characters which are special characters in regular expression, that may have appeared in the LIKE expression.

SYNOPSIS

like_to_rlike(expression TEXT CHARSET utf8)
  RETURNS TEXT CHARSET utf8

Input:

  • expression: a LIKE expression

EXAMPLES

mysql> SELECT like_to_rlike('customer%');
+----------------------------+
| like_to_rlike('customer%') |
+----------------------------+
| ^customer.*$               |
+----------------------------+

mysql> SELECT like_to_rlike('c_oun%');
+-------------------------+
| like_to_rlike('c_oun%') |
+-------------------------+
| ^c.oun.*$               |
+-------------------------+

ENVIRONMENT

MySQL 5.1 or newer

AUTHOR

Shlomi Noach
 
common_schema documentation