LIKE_REGEX
LIKE_REGEX is a SQL operation used to match a specific pattern in the data. It utilizes regular expressions to define the pattern. Regular expressions give more flexibility and control in data pattern matching compared to other LIKE operations in SQL.
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.
Example
SELECT first_name, last_nameFROM employeesWHERE REGEXP_LIKE(first_name, '^A');Output
FIRST_NAME LAST_NAME---------- ---------Allen RamirezAndrea ZimmermanArthur DouglasExplanation
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’.