SQL Reference - PATTERN

PATTERN

PATTERN in SQL is a part of the MATCH_RECOGNIZE clause used primarily in analyzing row sequences. It enables the detection of complex patterns within a data set by defining a regular expression pattern. Rows of the data set are evaluated against this pattern, facilitating the recognition of trends or anomalies in the data.

Example

SELECT * FROM Employees
WHERE Employee_Name LIKE '%son%'

Output

| Employee_ID | Employee_Name   | Department |
| ----------- | --------------- | -----------|
| 1           | Johnson Smith   | IT         |
| 2           | Samantha Watson | HR         |

Explanation

In this example, the LIKE pattern search is used in the WHERE clause to find all employees whose names contain the substring ‘son’. The ’%’ sign is used on both sides of ‘son’ to match any number of characters before and after ‘son’. As a result, it matches ‘Johnson Smith’ and ‘Samantha Watson’ from the ‘Employees’ table.

Example

SELECT
    title,
    description
FROM
    films
WHERE
    title LIKE 'Star%'
ORDER BY
    title;

Output

           title           |        description
---------------------------|--------------------------
 Star Wars                 | Epic space opera
 Star Trek                 | The voyages of starship
 Stargate                  | Interstellar teleportation device

Explanation

This SQL query selects the titles and descriptions of all films in films where the title begins with ‘Star’. The results are ordered alphabetically by title.

Example

SELECT * FROM employee WHERE REGEXP_LIKE (name, '^Ma');

Output

NAME    AGE   DEPARTMENT
Mark    35    Sales
Mary    28    IT
Maria   46    HR

Explanation

The given example code selects all records from the ‘employee’ table where the ‘name’ field starts with ‘Ma’. Regular expressions are used, and ’^’ signifies the beginning of the string. Therefore, this will return any employee who’s name starts with ‘Ma’.