Why do we use the SQL LIKE operator?
The LIKE operator is used to search column data by wildcards. For example:
--Percentage wildcard SELECT * FROM table_name WHERE col_name LIKE ‘M%’; --Underscore wildcard SELECT emp_name, emp_salary, join_date FROM sto_employees WHERE emp_name LIKE 'Be_';
- We have used various operators in different queries of SQL. For example, equal to (=), greater than (>), BETWEEN, IN, etc.
- In these operators, we provide value(s) and compare them with the column(s) for selecting the rows in the database tables.
- In many scenarios, we may not know the exact value (especially in the case of strings) to search or the users of our application require seeing all the results that match a certain pattern.
- For example, fetch all records from the employee table whose name starts with the letter ‘M’.
- Similarly, search all records based on a string column that contains the word “SQL” or contains the word “Computer” in the books column, etc.
- The percentage (%) sign – This represents any text of zero or more characters.
- The underscore (_) – represents a single character.
The next section shows you a number of queries using the LIKE SQL operator with these wildcards.
The syntax of using LIKE operator
SELECT * FROM table_name
WHERE match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ];
The example of using LIKE operator with ‘%’ wildcard
For the examples of the LIKE operator, I will use the demo table (sto_employees) that stores the information of employees.
In the first example, the query returns records of all employees whose name starts with the letter ‘J’.
See how the percentage sign is used with the LIKE operator for that:
SELECT * FROM sto_employees WHERE emp_name LIKE 'J%';
Search by specifying the last character only
The example below returns all records of those employees whose names end at ‘n’. For that, I used the ‘%’ sign as follows:
The LIKE query with ‘%’:
SELECT emp_name, emp_age, emp_salary FROM sto_employees WHERE emp_name LIKE '%n';
Similarly, you may specify two or more characters at the end of the specified column. For example:
Search records for a specified character anywhere
In this SQL LIKE statement, we will get the records of employees who have letter ‘a’ anywhere in their names.
SELECT emp_name, emp_age, join_date FROM sto_employees WHERE emp_name LIKE '%a%';
So, you may provide one or more characters in between the two percentage signs and the query will search values in the specified column.
Performing wildcard search on a numeric column examples
The following LIKE query searches a numeric column, emp_salary in our example table.
The query returns all records where the employee salary begins with the number ‘5’.
The LIKE query with a numeric column:
SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees WHERE emp_salary LIKE '5%';
From the query, we may learn that the LIKE operator works for numeric column searching just as in the case of the string column.
You may provide a numeric value at the beginning, end, or in-between to search by wildcard in the LIKE operator.
The example of using underscore (_) wildcard
The underscore wildcard substitutes only one character as using with LIKE operator. See a query of using this wildcard:
SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees WHERE emp_name LIKE 'Be_';
In the output, the query only retrieved Ben’s record, so a single underscore substituted ‘n’. You may use multiple underscore wildcards as well. For example:
SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees WHERE emp_name LIKE 'Mi__';
You may also mix up like this:
SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees WHERE emp_name LIKE '_im__';
Using % and _ together in LIKE operator
The following query returns records for those employees whose names have the second letter ‘a’ and fourth letter ‘n’.
The query with _ and %:
SELECT emp_name, emp_age FROM sto_employees WHERE emp_name LIKE '_a_n%';
Using multiple LIKE clauses in a single query
Multiple LIKE operators can be combined by “AND” & “OR” operators. So, if you wish to search for multiple columns you can do this easily.
The query below uses two LIKE operators with an OR operator:
SELECT emp_name, emp_age,emp_salary FROM sto_employees WHERE emp_name LIKE '_oh%' OR emp_salary LIKE '5%';