HomeDatabaseHow to use LIKE operator in SQL in 3 steps

How to use LIKE operator in SQL in 3 steps

The SQL LIKE Operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

This article will discusses how to use the LIKE operator for ltering rows.

SELECT col1, col2, ... coln
FROM table
WHERE col3 LIKE "%some-string%"
-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SELECT * from Actors WHERE FirstName LIKE "Jen%";
-- Query 2
SELECT * from Actors where FirstName LIKE "Jennifer%";
-- Query 3
SELECT * from Actors where FirstName LIKE "%";
-- Query 4SELECT * from Actors WHERE FirstName LIKE "_enn%";
-- Query 5
SHOW DATABASES LIKE "M%";
-- Query 6
SHOW TABLES LIKE "A%";

SELECT * from table WHERE column LIKE “Jen%”;

Say, you have forgotten the full name of a particular actor but remember that the name starts with the string “Jen”. You can check if there is an actor with a name that has “Jen” as the prefix by executing the following query in the terminal.

SELECT * from Actors WHERE FirstName LIKE "Jen%";
How to use LIKE operator in SQL in 3 steps

Note that we use the string “Jen%” and not “Jen”. In fact, if you used the latter, no rows will be matched.

How to use LIKE operator in SQL in 3 steps

The % symbol is a wildcard character that matches all strings. It can match zero or more characters. For instance, the following query returns one row and the wildcard character matches zero characters.

SELECT * from Actors where FirstName LIKE "Jennifer%";
How to use LIKE operator in SQL in 3 steps

As a final example, executing the following query will match all the rows in the table.

SELECT * from Actors where FirstName LIKE "%";
How to use LIKE operator in SQL in 3 steps

SELECT * from table WHERE FirstName LIKE “_enn%”;

We can use the underscore _ character to match exactly one character. For instance, the expression LIKE “_enn%” will match the string “Jennifer”.

SELECT * from Actors WHERE FirstName LIKE "_enn%";

SHOW DATABASES LIKE “M%”;

The LIKE clause can also be used with the SHOW command. For example:

SHOW DATABASES LIKE "M%";
How to use LIKE operator in SQL in 3 steps

Note that the LIKE statement is case sensitive. It shows different results for patterns “%M” and “%m”. We can also use LIKE when listing tables as the following example demonstrates:

SHOW TABLES LIKE "A%";

Admin
Onurdesk is a technical platform based primarily on Spring, Java, and Nodejs ecosystem app creation. We write tutorials and manuals concentrating extensively on realistic and real-life usage cases.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular

Recent Comments