LIKE Operator - SQL
Overview
The LIKE Operator in SQL is used to search for a specified pattern in a column. It is commonly used with wildcard characters to perform pattern matching within text data.
Wildcard characters:
% (Percent sign) - Represents zero , one, or multiple characters.
Example:
SELECT * FROM employees WHERE first_name LIKE 'J%'finds any first name starting with 'J'.
_ (Underscore) - Represents a single character.
Example:
SELECT * FROM employees WHERE first_name LIKE 'J_n'finds any three-letter first name starting with 'J' and ending with 'n'.
[] (Square brackets) - Represents any single character within the brackets.
Example:
SELECT * FROM employees WHERE first_name LIKE '[Jj]ohn'finds 'John' and 'john'.
[^] (Caret inside square brackets) - Represents any single character not within the brackets.
Example:
SELECT * FROM employees WHERE first_name LIKE '[^J]ohn'finds names like 'john', 'Bohn', but not 'John'.
[-] (Dash inside square brackets) - Represents a range of characters.
Example:
SELECT * FROM employees WHERE first_name LIKE '[A-C]%'finds any first name starting with 'A', 'B', or 'C'.
Syntax:
SELECT column
FROM table_name
WHERE column1 LIKE pattern
column1 LIKE pattern checks if the value in column1 matches the specified pattern.
Sample Data:
| department_id | first_name |
|---|---|
| 3 | Frank |
| 2 | Jane |
| 3 | Ashley |
| NULL | Glenn |
| 2 | Kelly |
| 1 | Richard |
| 1 | George |
| 5 | Kyle |
| 2 | James |
| 1 | Gustavo |
Example
In this example, we are returning the department_id and the first_name column. We then use the LIKE operator to filter the results to only include rows where the first_name column starts with the letter G.
Example:
SELECT department_id, first_name
FROM company.employees
WHERE first_name LIKE 'G%'