SQL: Wildcard Search With The LIKE Operator

Equality searches are great and efficient when you want exact matches or range of values. However, there will be times when you need to search a text field for not so perfect matches, perhaps a partial match is needed. Certain scenarios requires to search for patterns, such as an email address. That's when the LIKE operator is useful in SQL. The only caveat is that LIKE operators can only work with text fields.

Examples:

1. A word/text with a % at the end, searches for all the records that begins with the letters before the percent sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE 'Chef%'

The query above returns all the records in the Products table that begins with the word "Chef"

2. A word with % sign on both ends, means that the result will be any records that contains the enclosed word/text within the % sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Hot%'

The query above searches for any records that contains the word "Hot" in the ProductName field. It brings back all the records that contains the word "Hot" regardless of the position that it resides in.


3. A word/text with a % at the beginning, searches for all the records that ends with the word/text after the percent sign. It's works in kind of the reverse of what you think will happen

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Sauce'

The above query searches for all the records that ends with the word/text "Sauce" in the ProductName field in the Products table


4. Let's try something a little bit tricky. Let's say your boss wants you to search for a spread that he likes, but does not know the exact spelling for. He would tell you it's call something like a "boys" n "berry" spread. To get that .00001% raise that you've always wanted you told your boss, I can do it!. So how will you search for such a spread?

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Boy%y%'

The above query searches for a word that contains the text "Boy" and ends with the letter "y", and the result is, ta da! "Grandma's Boysenberry Spread" with that result you were able to get your .00001% raise and is finally able to afford half a Popsicle that you've been eyeing all week. All is well in the IT land once again.

Conclusion: The LIKE operator comes in handy when you need to match a text pattern in a text field. However, it takes longer to execute than an equality match. So use it sparingly, only when needed.