MySQL REGEXP_LIKE

The REGEXP_LIKE function in MySQL is used to perform regular expression matching in a query. It allows you to search for a specified pattern within a string column and retrieve rows that match the pattern. This function returns a boolean value, indicating whether the pattern is present in the specified column.

Syntax

Here is the syntax for the REGEXP_LIKE function:

REGEXP_LIKE(expression, pattern [, match_options])

expression: The string or column you want to search for the specified pattern.
pattern: The regular expression pattern you want to match.
match_options (optional): Additional options for the matching behavior, such as case sensitivity.

Example

Now, let’s look at an example to better understand how to use the REGEXP_LIKE function:

1. Suppose you have a table named employees with a column called employee_name. You want to retrieve all the employees whose names start with the letter “J” in a case-insensitive manner.

SELECT *
FROM employees
WHERE REGEXP_LIKE(employee_name, '^J', 'i');

In this example:

employee_name: The column you are searching.
^J: The regular expression pattern, where ^ indicates the start of the string, and J is the letter you want the name to start with.
‘i’: The match option for case-insensitive matching.

This query will return all rows from the employees table where the employee_name column starts with the letter “J” in a case-insensitive manner.

2. Here is another example that checks for a phone number with the format 123-456-7890:

SELECT REGEXP_LIKE('123-456-7890', '[0-9]{3}-[0-9]{3}-[0-9]{4}');

This will also return 1 because the string matches the pattern.

Conclusion

Keep in mind that the syntax and availability of functions may vary slightly across different versions of MySQL, so it’s a good practice to consult the official documentation for your specific MySQL version.