PostgreSQL – LIKE Operator

LIKE operator is used in the WHERE clause to filter results by pattern matching. LIKE operator uses 2 wildcard options. ‘%’ sign to match any number of characters(0,1, or more) and ‘_’ to match exactly 1 character.

Syntax:

LIKE '%abc%abc%';

LIKE '_abc';

Example:

Consider an employee table as shown below:

id	first_name	last_name	department	salary	email
101	bryan	walker	sales	50,000	bryan.walker@email.com
102	alex	carpenter	accounts	55,000	alex.carpenter@email.com
103	sam	white	sales	60,000	sam.white@egmail.com
104	john	barry	sales	54,000	john.barry@email.com
105	bill	wood	accounts	65,000
106	robert	hopkins	support	70,000
107	david	lee	support	72,000	david.lee@email.com
108	jack	sears	support	65,000
109	tom	young	sales	55,000	tom.young@email.com

Below is the SQL query to identify employees with invalid email addresses: The logic used here is if the ’email’ value does not contain ‘@’ it is considered invalid.

SELECT * FROM employee WHERE email not like '%@%';

Below is the SQL query to identify all employees with their first names starting with ‘a’

SELECT * FROM employee WHERE first_name like 'a%';

Below is the SQL query to identify all employees that have second character as ‘o’ in their first name.

SELECT * FROM employee WHERE first_name like '_o%';