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.

2019-06-17