How the RLIKE operator works in Mariadb?

The RLIKE operator in MariaDB is used to test whether a string matches a regular expression.

Posted on

The RLIKE operator in MariaDB is used to test whether a string matches a regular expression.

Syntax

The RLIKE operator has the following syntax:

expr RLIKE pattern [flags]

Where:

  • expr is the string to be tested.
  • pattern is the regular expression to be matched.
  • flags is an optional list of flags that can be used to modify the behavior of the RLIKE operator.

The following flags are available:

  • i: Case-insensitive matching.
  • m: Multiline mode.
  • s: Dot matches newline characters.
  • x: Extended mode.

Examples

The following examples demonstrate how to use the RLIKE operator:

Example 1: Basic matching

The following example shows how to use the RLIKE operator to test whether a string matches a basic regular expression:

SELECT 'This is a string' RLIKE '^This';

The output of the above query is:

1

This indicates that the string This is a string matches the regular expression ^This.

Example 2: Case-insensitive matching

The following example shows how to use the i flag to perform case-insensitive matching:

SELECT 'This is a string' RLIKE '^this' i;

The output of the above query is:

1

This indicates that the string This is a string matches the regular expression ^this even though the case of the letters is different.

Example 4: Dot matches newline characters

The following example shows how to use the s flag to allow the dot character to match newline characters:

SELECT 'This is a string
with multiple lines' RLIKE '.*';

The output of the above query is:

1

This indicates that the string matches the regular expression .* because the dot character matches newline characters when the s flag is enabled.

Example 5: Extended mode

The following example shows how to use the x flag to enable extended mode:

SELECT 'This is a string' RLIKE '(?i)^This';

The output of the above query is:

1

This indicates that the string This is a string matches the regular expression (?i)^This even though the case of the letters is different. The (?i) modifier enables case-insensitive matching within the regular expression.

The following functions are related to the RLIKE operator:

  • REGEXP: The REGEXP operator is a synonym for the RLIKE operator.
  • REGEXP_INSTR(): The REGEXP_INSTR function returns the position of the first occurrence of a regular expression in a string.
  • REGEXP_REPLACE: The REGEXP_REPLACE function replaces all occurrences of a regular expression in a string with a specified replacement string.

Conclusion

The RLIKE operator is a powerful tool for testing whether a string matches a regular expression. It can be used to perform a variety of tasks, such as validating input data, searching for patterns in text, and extracting data from text.