T-SQL 101: #22 Using the LIKE operator in T-SQL

I've discussed standard operators in previous posts but an interesting additional one is the LIKE operator. This allows for basic pattern matching in T-SQL predicates.

Let's look at a simple example:

WHERE ProductName LIKE 'Grant%'

The % symbol is a wild-card that matches any set of characters, including no characters at all. So this expression would match the following and more:

Grant
Grants Cola
Grants Lemonade

If we're looking for a word that's contained inside a string, we can use it like this:

WHERE ProductName LIKE '%Cola%'

That would match these (and others):

Grants Cola
Terry's Cola
Cola
Mary's Cola and Lime

In addition to the % symbol, we can specify sets of characters within square brackets. For example:

WHERE ProductName LIKE 'Jumbo [O,G]%Ale%'

That would match these (and others):

Jumbo Orange Ale
Jumbo Green Ale
Jumbo Ginger Ale

And within the square brackets, we can also put ranges of values. For example:

WHERE ProductName LIKE 'Jumbo Bin[3-5] Ale'

That would match these specifically:

Jumbo Bin3 Ale
Jumbo Bin4 Ale
Jumbo Bin5 Ale

There are other symbols that can be used. It's worth visiting the documentation page to see them. You'll find it here.

At this learning level, we're not worried about performance but it's worth noting that some of these operations (particularly those with leading % symbols) can lead to reading a lot of data.

Regardless, the LIKE operator is a useful option within the T-SQL language.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2 thoughts on “T-SQL 101: #22 Using the LIKE operator in T-SQL”

    1. Hi Mehmet,

      No, just didn't want to overwhelm in such an intro topic. But I'll add a note that there are other options.

      Thanks,
      Greg

Leave a Reply

Your email address will not be published.