SQL LIKE Operator
SQL LIKE Operator
The LIKE operator in SQL is used to match patterns in character strings. It is used in the WHERE clause of a SELECT statement to filter the results based on a specified pattern.
The LIKE operator is typically used with the percent sign (%) and the underscore (_) as wildcard characters. The percent sign represents zero, one, or multiple characters, while the underscore represents a single character.
Here is the basic syntax for using the LIKE operator:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
In this statement, column_name is the name of the column we want to search for the specified pattern, table_name is the name of the table containing the column, and pattern is the pattern we want to search for. The pattern can contain the wildcard characters (% and _).
Let’s take a look at some examples:
Suppose we have a table called employees that contains data about the employees of a company. The table has four columns: id, first_name, last_name, and email. We want to retrieve the names and email addresses of all the employees whose last name starts with the letter S.
SELECT first_name, last_name, email
FROM employees
WHERE last_name LIKE 'S%';
This query will return a result set with three columns: first_name, last_name, and email, containing the names and email addresses of all the employees whose last name starts with the letter S.
We can also use the underscore wildcard character to search for a single character:
SELECT first_name, last_name, email
FROM employees
WHERE first_name LIKE '_am%';
This query will return a result set with three columns: first_name, last_name, and email, containing the names and email addresses of all the employees whose first name starts with any character followed by am.
Note that the LIKE operator is not case-sensitive by default. If you want to perform a case-sensitive search, you can use the BINARY keyword:
SELECT first_name, last_name, email
FROM employees
WHERE last_name LIKE BINARY 's%';
This query will return a result set with three columns: first_name, last_name, and email, containing the names and email addresses of all the employees whose last name starts with the letter s (case-sensitive).