Blog

You probably already know that SQL SELECT queries are very powerful, and allow you to perform practically any data selection imaginable, especially when you have a mastery of them.

In this tutorial, to help you achieve you want 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?
• LIKE syntax in SQL
• SQL LIKE symbols
• LIKE SQL Examples
  • Example LIKE with percentage symbol %
  • Example LIKE with underscore symbol _
  • NOT LIKE SQL Example
  • Examples of like with several sql conditions

What is LIKE SQL used for?

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

Combining it with different symbols we can select rows from a table because a certain part of the text string meets a condition or, better said, pattern or patterns.

Examples of using the LIKE command 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 SQL), tell you that it would be something similar, although much simpler.

LIKE Syntax in SQL

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

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

   columna OPERADOR valor.

Although the more technical syntax is:

 match_expression [ NOT ] LIKE pattern

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 o metacharacters that exist to operate with LIKE.

Specifically, there are 2 symbols that will allow us to form different combinations:

1. The symbol % which means 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:

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

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

NOT LIKE SQL Example

So far I have shown you results with a positive search or, what is the same, the results that match the pattern.

But what if you want just the opposite?

For this you will use the NOT operator which, as always, returns TRUE when the result is the opposite of the condition that follows it.

Users with a name other than 6 letters:

SELECT id, nombre, apellidos
FROM usuario
WHERE nombre NOT LIKE '_____';

The result will be:

Examples of like with several sql conditions

And if we complicate things a bit?

For example, with LIKE and other conditions:

SELECT nombre, apellidos
FROM usuario
WHERE nombre LIKE '_____' AND id > 1;

As you can see, it can be combined with any other condition.

with operator AND or OR.

The result of the last query is:

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, tell you that although SQL LIKE in your WHERE is a vital and powerful operator to search for text strings, there is a more powerful option that is known as MATCH AGAINST SQL.