Blog

Surely you already know that SQL SELECT queries are very powerful, and allow you to perform practically any unimaginable data selection, especially when you have a high command of all the possibilities of SQL queries.

In this tutorial, to help you achieve more complex and powerful, I am going to explain the SQL LIKE operator.

Also, I’m going to show you several simple examples of LIKE SQL that will help you understand it quickly.

Content

• What is LIKE SQL used for?
• How is the LIKE operator used in SQL?
• SQL LIKE symbols
  • LIKE SQL Examples

What is LIKE SQL used for?

The LIKE operator has the objective of checking similarity conditions for texts.

Combining it with different symbols we will be able to select rows of a table because a certain part of a text fulfills a condition.

Examples of use of LIKE in SQL would:

• Select all texts that include a word. This is widely used in web browsers.
• Filter by a text field that has at least a certain number of characters.
• Perform checks of certain simple patterns, if you know regular expressions (RegExp), tell you that it would be something similar, although much simpler.

How is the LIKE operator used in SQL?

To use the LIKE operator in the conditions of your queries you only need to know 2 premises:

1. LIKE in SQL is used in conjunction with the WHERE clause.
2. the orLIKE is used similar to how you would an SQL IS or an “=”, i.e. it follows the logic of

   columna OPERADOR valor.

Here is a simple scheme of its use:

SELECT col1, col2
FROM tabla
WHERE col1 LIKE 'valor';

As we have said before, the LIKE operator should be used with symbols that allow checking for similarity to a particular pattern. Next I show you the types of existing patterns.

SQL LIKE symbols

Now I want to show you the symbols that exist to operate with LIKE. Specifically, there are 2 symbols that will allow us to form different combinations:

1. The symbol % meaning any 0 or more characters.
2. The symbol _ which means any 1 character.

LIKE SQL Examples

Next, and to end this tutorial, I am going to make several examples with a table that contains the following information:

LIKE with percentage symbol %

As I have commented before, the character % symbolizes any character that is repeated 0 or more times.

With examples you will understand it much better.

The following example would search for those users that his name begins with “jav”:

SELECT id, nombre, apellidos
FROM usuario
WHERE nombre LIKE 'Jav%';

The results of this query would be:

Now I am going to select the users whose last name ends with “ez”:

SELECT *
FROM usuario
WHERE apellidos LIKE '%ez';

And the result of this query with LIKE would be:

Query to select users whose last name contains “my”:

SELECT *
FROM usuario
WHERE apellidos LIKE '%mi%';

With result:

Now query with LIKE which gets those users whose last name contains an L, an R, and a Z in this order:

SELECT *
FROM usuario
WHERE apellidos LIKE '%L%R%Z%';

And your result would be:

LIKE with underscore symbol _

Just as we have seen before that the percentage symbol meant the appearance of 0 or more any characters, the low bar means the appearance of 1 unspecified character.

For example, the following SELECT with LIKE Filter all those users who have a name with 6 letters:

SELECT *
FROM usuario
WHERE nombre LIKE '_____';

The result of this selective filtering would be:

Once you have seen this last example, I think you can imagine the rest of the combinations, as you already imagined, there are no limits when it comes to combining one symbol with another, so that in the same LIKE condition can appear as many portages as low bar (underscore) you want.

You should also know that a WHERE condition can contain multiple LIKEs with the same or different columns without problem. Always joining them with OR or AND just like you would with any other condition.

Finally, let me tell you that although LIKE is a useful and powerful operator to search for text strings, there is a more powerful option known as MATCH AGAINST SQL.