SQL LIKE Operator

A visual graphic shows what is SQL LIKE operator. It also shows important points about the LIKE along with example queries with percentage and underscore wildcards.

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 SQL LIKE operator enables us to search a column by a specified pattern. The LIKE uses two wildcards for performing the search:
  • 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:

Query:

SELECT * FROM sto_employees

WHERE emp_name LIKE 'J%';

SQL LIKE

In the query, this can be learned that if you place a ‘%’ sign after a character(s), it will search any number of characters in the specified column ahead of this.

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';

The result-set:

SQL LIKE last

Similarly, you may specify two or more characters at the end of the specified column. For example:

WHERE emp_name like ‘%on’;

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%';

SQL LIKE anywhere

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%';

The result-set:

SQL LIKE numeric

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_';

The output:

SQL LIKE underscore

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__';

The result:

SQL LIKE multiple

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%';

SQL LIKE both

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%';

The result-set:

SQL LIKE two

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!