In SQL, we do not have access to the StartsWith() operator that can allow us to perform the text-based filtering by including or excluding any value that starts with a specific pattern.
However, in databases like MySQL, we can use the LIKE operator which allows us to perform basically the same operations
In this tutorial, we will walk you through everything you need to know when working with the LIKE operator. We start with the basics and then progress to more complex and practical applications.
NOTE: For this tutorial, we will demonstrate how to use this operator using the MySQL database, specifically the MySQL version 80. But this is bound to work on MySQL 5.0 as well.
MySQL LIKE Operator
In SQL, we use the LIKE operator to filter the rows from a given table based on the specific character or set of characters that a string begins with a specified prefix.
A common use case is retrieving the records that match a certain pattern at the beginning of a column value.
We often use the LIKE operator conjunction with the SELECT statement to retrieve the data that meets the specified criteria.
Although the syntax and formatting may vary depending on how the operator is used, the following shows the basic syntax of the operator in a SELECT statement:
FROM table_name
WHERE column_name LIKE 'prefix%';
In the given syntax:
- column1, column2, …: – This represents the columns from which we wish to retrieve the data.
- table_name – This sets the name of the table that we wish to query.
- column_name – This defines the name of the column that we wish to filter.
- ‘prefix%’ – We have the prefix which allows us to specify a pattern that we wish to search for where “%” represents zero or more characters.
Example Usage: Find the Films that Start with Z
Let us look at more practical examples on how to use this operator for more understanding. For demonstration purposes, we use the MySQL Sakila sample database.
Feel free to download and install it or use your own dataset.
Let us take the “film” table from the Sakila database for example. Suppose we wish to find out any film whose title begins with the letter “Z”. We can run a query as follows:
FROM film f
WHERE title LIKE 'Z%';
In this query, we use the LIKE operator to fetch the records where the title of the film starts with the letter “Z”. In the prefix, we use the letter Z with the “%” wildcard which denotes one or more characters.
The resulting output is as follows:
Conclusion
In this tutorial, we learned how we can use the MySQL LIKE operator to perform the character matching. This includes the use of “%” wildcard to search for patterns at the beginning of a column value.
