MySQL Where LIKE
In MySQL, the LIKE
operator is used to search for a specified pattern in a string column. It is commonly used in the WHERE
clause of a query to retrieve rows that match a particular pattern.
The syntax for the LIKE
operator is as follows:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
In this syntax, column_name(s)
is the name of the column or columns that you want to retrieve data from, table_name
is the name of the table that contains the data, and pattern
is the string pattern that you want to search for. The pattern
can contain wildcard characters that match any string of any length, including %
(percent sign) and _
(underscore).
Here is an example of using the LIKE
operator to retrieve all rows from a table named users
where the email
column contains the string “gmail.com”:
SELECT *
FROM users
WHERE email LIKE '%gmail.com';
In this example, the %
wildcard matches any string of any length before “gmail.com”. This means that any email address that ends with “gmail.com” will be included in the result set.
You can also use the LIKE
operator to search for patterns at the beginning or end of a string. For example, to retrieve all rows from the users
table where the email
column starts with “john”:
SELECT *
FROM users
WHERE email LIKE 'john%';
In this example, the LIKE
operator searches for any email address that starts with “john”.
To search for a specific character within a string, you can use the underscore (_
) wildcard. For example, to retrieve all rows from the users
table where the email
column contains an underscore followed by “gmail.com”:
SELECT *
FROM users
WHERE email LIKE '_@gmail.com';
In this example, the _
wildcard matches any single character. This means that any email address that contains an underscore followed by “gmail.com” will be included in the result set.
In conclusion, the LIKE
operator in MySQL is a powerful tool for searching for patterns in string columns. It allows developers to easily retrieve data that matches a particular pattern, making it a valuable tool for data analysis and management.
Table structure Used for Example explanation
+————-+———-+
| Field | Type |
+————-+———-+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+————-+———-+
Example for MySQL LIKE Operator
select name from city where name like(‘A%’);
Output of Above Example - MySQL Where Like
+——————-+
| name |
+——————-+
| Amsterdam |
| Apeldoorn |
| Almere |
| Arnhem |
| ´s-Hertogenbosch |
| Amersfoort |
| Alkmaar |
| Alger |
| Annaba |
+——————-+
9 rows in set (0.07 sec)