A Brief About LIKE Operator in SQL

The LIKE operator is used to search column’s data by wildcards. For example:

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 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 whom name start with the letter ‘M’.

Similarly, search all records based on a string column that contains word “SQL” or contains word “Computer” in the books column etc.

The SQL LIKE operator enables us searching a column by specified pattern. The LIKE uses two wildcards for performing the search:

  • The percentage (%) sign – This represents any string of zero or more characters.
  • The underscore (_) – it represents a single character.

The next section shows you a number of queries of using LIKE SQL operator with these wildcards; so keep reading.

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 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 which name starts with letter ‘J’. See how percentage sign is used with LIKE operator for that:

Query:

SQL LIKE

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

Search by specifying last character only

The example below returns all records of those employees which names end at ‘n’. For that, I used the ‘%’ sign as follows:

The LIKE query with ‘%’:

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.

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 employee salary begins with number ‘5’. Have a look:

The LIKE query with numeric column:

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 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:

The output:

SQL LIKE underscore

In the output, the query only retrieved Ben’s record, so single underscore substituted ‘n’. You may use multiple underscore wildcards as well. For example:

You may also mix up like this:

And the result is:

SQL LIKE multiple

Using % and _ together in LIKE operator

The following query return records for those employees which names have second letter ‘a’ and fourth letter ‘n’.

The query with _ and %:

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 OR operator:

The result-set:

SQL LIKE two