PATTERN

MySQL

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.

PostgreSQL

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.

Oracle

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’.