SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL LIKE
In this tutorial, you will learn how to retrieve the data that matches a pattern using the SQL operator LIKE within the WHERE clause.
The LIKE is one of the logical operators in SQL that returns TRUE if a value matches a pattern or FALSE otherwise.
| AND | Return True if both conditions are True |
| OR | Return True if one of conditions is True |
| NOT | Reverse the result of any Boolean operator |
| IN | Return True if a value is in a set of values |
| BETWEEN | Return True if a value falls within a specific range |
| LIKE | Return True if a value matches a pattern |
The SQL provides you with two wildcard characters to build up a pattern:
- The percent sign (%) – matches anything, zero, one or multiple characters.
- The underscore (_) – matches exactly one character.
This is not easy to understand lets have some examples.
Syntax
The basic syntax of the WHERE clause to filter the data returned by a query can be given with:
SELECT column_names
FROM table_name
WHERE column_name LIKE value
Examples
To understand the LIKE statement in a better way, let’s look at the following customers table in our tutorial database:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
Now, let’s check out some examples that demonstrate how it actually works.
We have the following task be to solve using SQL statements
The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.
You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.
SELECT *
FROM customers
WHERE first name LIKE 'M%'
After executing the above query, you’ll get the result set something like this:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
As you can see the output contains everything the whole customers tables including all rows and columns.
Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.
SELECT *
FROM customers
WHERE first name LIKE '%n'
SELECT *
FROM customers
WHERE first name LIKE '%r%'
Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.
Operators in WHERE Clause
You can filter your results in a number of ways using comparison and logical operators, which you’ll learn about in the next tutorials. I summarized in the following table the most important ones.
SELECT *
FROM customers
WHERE first name LIKE '__r%'
