MySQL regexp_like() Function
The Regexp_like is a new function introduced in MySQL 8. It compares a string with a regex pattern and returns whether they match or not. The Regexp_like command gives back a 1 if there is a match or 0 if there is no match.
The Basic Syntax of regexp_like() is,
REGEXP_LIKE (expression, pattern [, match_type])
Where,
expression: It is an input string on which we perform searching for matching the regular expression.
pattern: It represents the regular expression for which we are testing the string.
match_type: It is a string that allows us to refine the regular expression. It uses the following possible characters to perform matching.
- c: case-sensitive matching.
- i: case-insensitive matching.
- m: multiple-line mode that recognizes line terminators within the string. By default, this function matches line terminators at the start and end of the string.
- n: It is used to modify the. (dot) character to match line terminators. By default, it will stop at the end of a line.
- u: It represents Unix-only line endings that recognize only the newline character by the ., ^, and $ match operators.
Examples
Below are a few examples of the regexp_like functions. In the examples below we use the regexp_like function in the select statement. However we can use theregexp_like in the where clause as well.
-
Basic regexp_like usage
select regexp_like('new*\n*line', '.*');
2. Regexp_like to see if characters follow a particular format.
This checks if the string has the pattern TE one or more times in the string.
SELECT regexp_like('ARACHNOPHOBIA ROLLERCOASTER','TE+');
3. Regexp_like to see if the characters are present or not.
select regexp_like('Questionnaire,Bonanza,Network','[y]'),regexp_like('Questionnaire,Bonanza,Network','[^y]');
