SQL Reference - MATCHES

Example

SELECT *
FROM Student
WHERE Name REGEXP '^J';

Output

| ID | Name  | Grade |
|----|-------|-------|
| 1  | John  | B     |
| 3  | James | A     |

Explanation

The example SQL command is a SELECT statement that returns all the records of students whose names begin with the letter ‘J’ in the ‘Student’ table. The REGEXP operator is used for complex searches using regular expressions, in this case ‘^J’ which means any string that starts with ‘J’.

Example

SELECT name
FROM artists
WHERE name ~* 'john';

Output

     name
----------------
  John Lennon
  Johnny Cash
(2 rows)

Explanation

The above SQL code is used to search all artist names in the artists table that match the pattern ‘john’. The tilde (~) character combined with an asterisk (*) uses PostgreSQL’s regular expression capabilities to perform a case-insensitive pattern match. This returns all artist names containing ‘john’ regardless of their case (whether it’s ‘John’, ‘john’, ‘JOHN’, etc.)

Example

SELECT username
FROM users
WHERE username LIKE 'Joh_';

Output

username
--------
John
Joha
Johm

Explanation

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. The ”%” symbol is used to define wildcards (missing letters) both before and after the pattern. In the given SQL code, a search in the username column of the users table is performed for a pattern ‘Joh_’. The underscore represents a single character. Therefore, it will return all usernames that start with ‘Joh’ and have any character as forth symbol. You would see matching results as ‘John’, ‘Joha’, ‘Johm’ etc.

Example

SELECT employee_name
FROM employees
WHERE REGEXP_LIKE (employee_name, '^Ste.*');

Output

Steve
Stella
Stephen

Explanation

The REGEXP_LIKE function is used to perform pattern matching. Here, it matches employee names that start with ‘Ste’. The ’^’ indicates the start of the string, and ’.*’ allows for any characters following ‘Ste’.