LIKE_REGEX

MySQL

Example

SELECT name FROM employees WHERE name REGEXP '[A-Z]$';

Output

+------+
| name |
+------+
| John |
| Paul |
+------+

Explanation

In the given SQL statement, we are using the REGEXP keyword as a comparator to filter the rows from the ‘employees’ table based on the provided regular expression ‘[A-Z]$’. This regular expression matches any string that ends with an uppercase letter. As a result, the names ‘John’ and ‘Paul’, which end with an uppercase letter, are returned.

Oracle

Example

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^A');

Output

FIRST_NAME LAST_NAME
---------- ---------
Allen      Ramirez
Andrea     Zimmerman
Arthur     Douglas

Explanation

The REGEXP_LIKE function in Oracle is used to search character data. In the provided example, it returns the first and last names of all employees whose first names start with the letter ‘A’. The ‘^A’ is a regex pattern where ’^’ signifies the start of a string and ‘A’ is the character we are matching. Thus, ‘^A’ matches any string that starts with ‘A’.