LIKE Operator in SQL
In the previous tutorials, we have seen the basic operators that play a key role in the SQL databases. Now, we shall discuss LIKE operator in SQL. LIKE operator is a wildcard useful for filtering of the data. The LIKE operator instructs the database to compare the characters that match with some part of the data.
This wildcard filtering is only possible for the data types of type string. The LIKE operator can be used in combination with the WHERE and SELECT commands.
Syntax of LIKE Operator
SELECT col1, co12…… col3
FROM table_name
WHERE col1 LIKE ‘string’The basic syntax of the LIKE operator will be similar to the above one. But, the LIKE operator can be written is multiple formats to filter the data in an effective manner.
The Percentage Sign (%) Wildcard
Percentage wildcard is frequently used to obtain the data similar to the particular word. This wildcard is useful to obtain the occurrences of all the matches in the data.
Example
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘Fish%’;
Output
prod_id prod_name
——- ——————
BNBG01 Fish bean bag toy
Similar to the above case, to match the data on both sides for a particular word, then we have to define in the following way.
Example
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘%bean bag%’;
Output
prod_id prod_name
——– ——————–
BNBG01 Fish bean bag toy
BNBG02 Bird bean bag toy
BNBG03 Rabbit bean bag toy
The percentage wildcard can also be used over a particular word to obtain similar data relative to either side of the word.
Example
SELECT prod_name
FROM Products
WHERE prod_name LIKE ‘F%y’;
The output sometimes might end up with the zero similarities, that shows there is no match with the data in the database.
The Underscore(_) Wildcard
The other most useful wildcard is the underscore wildcard. This performs the filtering similar to the percentage sign, but only matches one character instead of multiple.
Let us look into the example to better understand it.
Example
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘__ inch teddy bear’;
Output
prod_id prod_name
——– ——————–
BNBG02 12 inch teddy bear
BNBG03 18 inch teddy bear
Now, we shall perform the above example matching using the %wildcard and look into the clear conclusions about underscore wildcard.
Example
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘% inch teddy bear’;
Output
prod_id prod_name
——– ——————–
BNBG01 8 inch teddy bear
BNBG02 12 inch teddy bear
BNBG03 18 inch teddy bear
The % wildcard matches all the characters with zero to the maximum similarities as possible. But the _ wildcard will match only one character, not more or less. In the above example using the _wildcard, two search patterns are used, which matches the characters with two letters.
The Brackets([]) Wildcard
This wildcard contains the set of characters and matches the characters in the specified position. Unlike the previous wildcards, some databases cannot support to work with the bracket wildcard.
For example, if you want to find the data whose name matches with the letter J or M you can use the bracket wildcard in the following way.
Example
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[JM]%’
ORDER BY cust_contact;
Output
cust_contact
—————–
Jim Jones
John Smith
Michelle Green
Now, similarly, you can get the list of the data except the names that start with the letters J and M, as described in the below example.
Example
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[^JM]%’
ORDER BY cust_contact;
This is all the end of the LIKE operator in SQL. For any sort of doubts or the questions in this tutorial, you can reach out through the comment box.
Related Tutorials