The SQL LIKE operator is used in a WHERE clause to search for a specific pattern in a column. The LIKE operator is often used with the % wildcard, which can match any number of characters, including none.
The syntax for the LIKE operator is as follows:
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
Where “column_name” is the name of the column you want to search, “pattern” is the pattern you are looking for, and “table_name” is the name of the table that contains the column.
In the SQL LIKE operator, different symbols are used to match patterns in a string:
%(Percentage Sign): Matches any number of characters, including none._(Underscore): Matches any single character.[ ](Brackets): Matches any single character within the specified range (for example,[a-z]matches any lowercase letter).[^ ](Caret within Brackets): Matches any single character not within the specified range (for example,[^A-Z]matches any character that is not an uppercase letter).
Here are some examples:
LIKE 'J%'matches any string that starts with “J”.LIKE '%Smith'matches any string that ends with “Smith”.LIKE '%oe%'matches any string that contains “oe”.LIKE '_ohn'matches any string that has exactly four characters and the second character is “o”.LIKE '[Jj]ane'matches any string that starts with either “Jane” or “jane”.
The LIKE operator is case-sensitive by default, but some databases offer case-insensitive options.
Sample Data Table
Consider the following “employees” table:
| ID | Name | Department |
|---|---|---|
| 1 | John Doe | Sales |
| 2 | Jane Doe | Marketing |
| 3 | Bob Smith | IT |
| 4 | Tom Jones | Sales |
| 5 | Sophie Turner | HR |
SQL LIKE Operator Examples
Let’s go over some examples of using the SQL LIKE operator:
Example 1: Finding employees with names starting with “J”
To find all employees whose names start with “J”, you would run the following query:
SELECT * FROM employees WHERE Name LIKE 'J%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 1 | John Doe | Sales |
| 2 | Jane Doe | Marketing |
Example 2: Finding employees with names ending with “Smith”
To find all employees whose names end with “Smith”, you would run the following query:
SELECT * FROM employees WHERE Name LIKE '%Smith';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 3 | Bob Smith | IT |
Example 3: Finding employees with names containing “oe”
To find all employees whose names contain “oe”, you would run the following query:
SELECT * FROM employees WHERE Name LIKE '%oe%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 1 | John Doe | Sales |
| 2 | Jane Doe | Marketing |
Example 4: Finding employees with departments starting with “S”
To find all employees whose department names start with “S”, you would run the following query:
SELECT * FROM employees WHERE Department LIKE 'S%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 1 | John Doe | Sales |
| 4 | Tom Jones | Sales |
Example 5: Finding employees with departments containing “ark”
To find all employees whose department names contain “ark”, you would run the following query:
SELECT * FROM employees WHERE Department LIKE '%ark%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 2 | Jane Doe | Marketing |
Example 6: Finding employees with names starting with “T” and ending with “Jones”
To find all employees whose names start with “T” and end with “Jones”, you would run the following query:
SELECT * FROM employees WHERE Name LIKE 'T%Jones';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 4 | Tom Jones | Sales |
Example 6: Finding employees with names starting with “D” or “T”
To find all employees whose names start with “D” or “T”, you would run the following query:
SELECT * FROM employees WHERE Name LIKE 'D%' OR Name LIKE 'T%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 2 | Jane Doe | Marketing |
| 4 | Tom Jones | Sales |
Example 7: Finding employees with departments ending with “eting” or “es”
To find all employees whose departments end with “eting” or “es”, you would run the following query:
SELECT * FROM employees WHERE Department LIKE '%eting' OR Department LIKE '%es';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 1 | John Doe | Sales |
| 2 | Jane Doe | Marketing |
| 4 | Tom Jones | Sales |
Example 8: Finding employees with names containing “o” in the second position
To find all employees whose names contain “o” in the second position, you would run the following query:
SELECT * FROM employees WHERE Name LIKE '_o%';
This would return the following result:
| ID | Name | Department |
|---|---|---|
| 3 | Bob Smith | IT |
| 4 | Tom Jones | Sales |
In conclusion, the SQL LIKE operator is a powerful tool for searching for specific patterns within columns in a database. The use of symbols such as the percentage sign, underscore, brackets, and caret within brackets allows for a range of options to match any given pattern. The operator is case-sensitive by default, but some databases offer case-insensitivity options. Through the use of examples and a sample data table, it has been demonstrated how the SQL LIKE operator can be used to search for patterns in the names of employees within a database. Overall, the LIKE operator is a useful tool for querying data within a database and should be considered when searching for specific patterns within a column.
