LIKE Operator in SQL
LIKE Operator in SQL
Structured Query Language (SQL) is a programming language that has several commands, operators and functions. SQL Is said to be the best in maintaining efficient database systems. You can create data, duplicate data, store data, clear the unnecessary data and do many other functions using SQL. When you need to create data based on some previously existing format or specifications, then you can use the “Like” Operator.
This tutorial will cover the concepts of,
- What is the purpose of the Like Operator?
- Syntax format of the Like Operator
- Various Patterns that can be retrieved back using the Like Operator
- Examples and Applications
Scroll below to learn the concepts.
What is the Purpose of Like Operator?
Consider you have a table A. Let’s name it the table Flower. The other table you are creating is, table B – Table Animals. You want to put different data in the table Animals, in the same pattern and tabulation that you followed previously in table flower. This is more like the Replication of the Table with Different data put in. For this function to be performed, “Like Operator” helps you.
This Like Operator is used along with the “Where” clause in the SELECT, UPDATE, DELETE statements to retrieve data based on a specific pattern. There are a few wildcards that this operator would use. They are as,
- ( %) – To match No character or More characters
- ( _ ) – To match exactly one character and no more than that.
- ( ^ ) – To match any character that is not within a range.
Now, you would have understood the purpose of the Like Operator. Let us know about the syntax in which this operator is used.
Syntax of the Like Operator:
There are specific formats and syntax with which every operator is written to function. Without syntax, you cannot perform the required action. The format and syntax of the Like Operator looks like
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE pattern;
Every command followed in SQL language has to be in the perfect format, without which you cannot perform any action. SQL is case and symbol sensitive, for every simple mistake you put in, you will not be able to perform the required action. Hence, always follow the correct pattern and syntax whenever you type a command in SQL.
Various Patterns that can be retrieved back using the Like Operator:
With the like operator, you can retrieve or create several data based on similar patterns. This tutorial will help you understand a few patterns which can be retrieved using the Like Operator.
Query 1: To find values starting with “a”
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE “a%”
With this Syntax, you can find the values that begin with “a” and continue to a great extent.
Query 2: To find values that end with “b”
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE ‘%b’
With this syntax, you can find the values that start with any value and end with “b”.
Query 3: To find the values that have “XYZ” that lies in between in any position
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE ‘%XYZ%’
Consider XYZ is in between the many columns and rows of the table and you want to find it. This query will help you to find the exact position of the values that you want to find.
Query 4: To find the value that has “b” in the fourth position
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE ‘___b%’
Underscores in SQL denote the exact value that you have to find. So, if you want to find the fourth value, including Three underscores (_) and the exact value will straightly take you to the fourth position that has the value “b” in it.
Query 5: To find the values that start with ABC and end with XYZ
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE ‘abc%xyz’
To find the series of numbers that begin with ABC and end with XYZ, this syntax of Like Operator will prove helpful.
Query 6: To find the values that begin with “b” and measures 5 characters in length.
Syntax:
SELECT column1, column2, column3,......,columnN FROM tablename WHERE columnName LIKE ‘b____%’
This syntax contains four underscores after “b” that indicates that our search is for a five-character word. Including a “%” at the end denotes that our search is a series of characters and not just a single letter character.
Examples of using LIKE Operator in SQL
Consider a food menu card of a restaurant that has listed its varieties along with its price and serial number.
| Serial Number | Food Item | Price |
| 1 | Idly | Rs.30 |
| 2 | Dosa | Rs.50 |
| 3 | Idiyappam | Rs.80 |
| 4 | Dal Kichadi | Rs.75 |
| 5 | Chapati | Rs.80 |
Query 1: To select all the food items that begin with ‘I’
SELECT SerialNumber, FoodItem, Price FROM FoodMenu WHERE FoodItem LIKE ‘I%’
Your Output will consist of 2 records that include
| Serial Number | Food Item | Price |
| 1 | Idly | Rs.30 |
| 3 | Idiyappam | Rs.80 |
Query 2: To select all the food items that end with the letter ‘I’
SELECT SerialNumber, FoodItem FROM FoodMenu WHERE FoodItem LIKE ‘%I’
Your Output will include Two records that are,
| Serial Number | Food Item |
| 4 | Dal Kichadi |
| 5 | Chapati |
The SELECT Statement consists of only two object parameters and hence only the Serial Number column and Food Item column are displayed for this query.
Query 3: List out the food items that start with ‘Idi’ and have 6 more characters associated with it.
SELECT FoodItem, Price FROM FoodMenu WHERE FoodItem LIKE ‘Idi______%’
Out of the FoodMenu Table given in here, There is only one item that depicts the given requirement and hence the output will bring only one record.
| Food Item | Price |
| Idiyappam | Rs.80 |
The above-given commands and queries are all examples of the LIKE Operator being used in SQL. One can easily understand by reading the above content to know the detailed usage of SQL Commands in real-time.
