Example analysis of regexp_like() function in mysql

In MySQL, the REGEXP_LIKE() function is used to determine whether a string matches a regular expression.

If the string matches the provided regular expression, the function returns 1; if it does not match, it returns 0.

Syntax:

REGEXP_LIKE(expr, pat[, match_type])

where expr is the input string and pat is the regular expression of the test string.

The optional match_type parameter allows you to refine the regular expression.

For example, you can use match_type to specify a case-sensitive match or not.

regexp_like() Example 1 - Basic usage

The following is a basic example:

SELECT REGEXP_LIKE('Cat', '.*') Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

In this case, the regular expression specifies any character in any sequence, so of course we get a match. The function returns 1 to indicate a match.

regexp_like() Example 2 - No match

The following is an example where the input string does not match the regular expression:

SELECT REGEXP_LIKE('Cat', 'b+') Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

In this case, the regular expression specifies that there should be one or more b characters in any sequence. Our input string does not contain this character, so 0 is returned.

regexp_like() Example 3 - Match the beginning of a string

The following is an example of a regular expression specifying that a string must begin with certain characters:

SELECT REGEXP_LIKE('Cat', '^Ca') Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

What happens if there is no match:

SELECT REGEXP_LIKE('Cat', '^Da') Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

regexp_like() example 4 - match_type parameter

You can provide additional parameters to determine the match type. This allows you to specify whether the match is case-sensitive, includes line terminators, etc.

The following is an example of specifying case-sensitive matching and case-sensitive matching:

SELECT 
  REGEXP_LIKE('Cat', '^ca', 'c') 'Case-Sensitive',
  REGEXP_LIKE('Cat', '^ca', 'i') 'Case-Insensitive';

Result:

+----------------+------------------+
| Case-Sensitive | Case-Insensitive |
+----------------+------------------+
|              0 |                1 |
+----------------+------------------+

The match_type parameter can contain the following characters:

c: Case-sensitive matching.

i: Matches regardless of case.

m: Multi-line mode. Identify line terminators in strings. The default behavior is to match line terminators only at the beginning and end of a string expression.

n: The . character matches the line terminator. The default setting is . matches to stop at the end of the line.

u: Unix-only line endings. Only newline characters are recognized as line terminators by the ., ^, and $ matching operators.

Related recommendations: "mysql tutorial"

The above is the detailed content of Example analysis of regexp_like() function in mysql. For more information, please follow other related articles on the PHP Chinese website!