REGEXP_LIKE()

Description

Returns TRUE if the string expr matches the regular expression specified by the pattern pat, FALSE otherwise. If expr or pat is NULL, the return value is NULL.

REGEXP and RLIKE are synonyms for REGEXP_LIKE().

Syntax

  1. > REGEXP_LIKE(expr, pat[, match_type])

Explanations

  • expr is the string to search for.

  • pat is a regular expression to look for in the string.

  • match_type: The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:

    • 'c': Case-sensitive matching by default.
    • 'i': Case-insensitive matching.
    • 'n': The . character matches line terminators. The default is for . matching to stop at the end of a line.
    • 'm': Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
    • 'u': Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

Examples

  1. mysql> SELECT REGEXP_INSTR('Hello, my number is 12345.', '[0-9]+');
  2. +--------------------------------------------------+
  3. | regexp_instr(Hello, my number is 12345., [0-9]+) |
  4. +--------------------------------------------------+
  5. |                                               21 |
  6. +--------------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> SELECT REGEXP_INSTR('apple', 'z+');
  10. +-------------------------+
  11. | regexp_instr(apple, z+) |
  12. +-------------------------+
  13. |                       0 |
  14. +-------------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
  18. +-----------------------------------+
  19. | regexp_like(CamelCase, CAMELCASE) |
  20. +-----------------------------------+
  21. | false                             |
  22. +-----------------------------------+
  23. 1 row in set (0.01 sec)
  25. mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE', 'i');
  26. +--------------------------------------+
  27. | regexp_like(CamelCase, CAMELCASE, i) |
  28. +--------------------------------------+
  29. | true                                 |
  30. +--------------------------------------+
  31. 1 row in set (0.01 sec)