YouTipSql Like
# SQL LIKE Operator
* * *
The LIKE operator is used in the WHERE clause to search for a specified pattern in a column.
The `LIKE` operator is a keyword in SQL used for fuzzy queries within the `WHERE` clause. It allows us to select data based on pattern matching, typically used with the `%` and `_` wildcards.
### SQL LIKE Syntax
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
Parameter Explanation:
* **column1, column2, ...**: The field names to select. Multiple fields can be specified. If no field names are specified, all fields will be selected.
* **table_name**: The name of the table to query.
* **column**: The field name to search.
* **pattern**: The search pattern.
**Wildcards**
* `%`: Matches any number of characters (including zero characters).
* `_`: Matches a single character.
### Example
Assume we have a table named Products with the following data:
| ProductID | ProductName | Category |
| --- | --- | --- |
| 1 | iPhone 12 | Electronics |
| 2 | Samsung Galaxy S21 | Electronics |
| 3 | Dell XPS 13 | Electronics |
| 4 | Nike Air Zoom | Footwear |
| 5 | Adidas Ultraboost | Footwear |
| 6 | Sony PlayStation 5 | Electronics |
Use the % wildcard to find all products starting with "iPhone":
SELECT ProductName, Category FROM Products WHERE ProductName LIKE 'iPhone%';
Returns the following data:
| ProductName | Category |
| --- | --- |
| iPhone 12 | Electronics |
Use the _ wildcard to find all products where the second character in the product name is "e":
SELECT ProductName, Category FROM Products WHERE ProductName LIKE '_e%';
Returns the following data:
| ProductName | Category |
| --- | --- |
| Dell XPS 13 | Electronics |
Combine % and _ wildcards to find all products containing "Zoom" in the product name:
SELECT ProductName, Category FROM Products WHERE ProductName LIKE '%Zoom%';
Returns the following data:
| ProductName | Category |
| --- | --- |
| Nike Air Zoom | Footwear |
* * *
## Demo Database
In this tutorial, we will use the TUTORIAL sample database.
Below is data selected from the "Websites" table:
mysql> SELECT * FROM Websites;+----+---------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+---------------+---------------------------+-------+---------+| 1 | Google | https://www.google.cm/ | 1 | USA || 2 | Taobao | https://www.taobao.com/ | 13 | CN || 3 | Tutorial | | 5000 | USA || 4 | Weibo | http://weibo.com/ | 20 | CN || 5 | Facebook | https://www.facebook.com/ | 3 | USA || 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |+----+---------------+---------------------------+-------+---------+
* * *
## SQL LIKE Operator Examples
The following SQL statement selects all customers where the name starts with "G":
## Example
SELECT * FROM Websites
WHERE name LIKE 'G%';
Execution output:
!(#)
**Note:** The "%" symbol is used to define wildcards (default letters) before and after the pattern. You will learn more about wildcards in the next chapter.
The following SQL statement selects all customers where the name ends with "k":
## Example
SELECT * FROM Websites
WHERE name LIKE '%k';
Execution output:
!(#)
The following SQL statement selects all customers where the name contains the pattern "oo":
## Example
SELECT * FROM Websites
WHERE name LIKE '%oo%';
Execution output:
!(#)
By using the NOT keyword, you can select records that do not match the pattern.
The following SQL statement selects all customers where the name does not contain the pattern "oo":
## Example
SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';
Execution output:
!(#)