Finding Matches Using LIKE With Square Bracket In MS SQL Server

In MS SQL Server, a common way to filter out a query or to get records with matching patterns is to pair a WHERE condition with the keyword LIKE.

Usually, it's much easier to pinpoint an exact match if you pair a WHERE condition with a constant value.

For example:

In a sample database provided by Microsoft, the AdventureWorks2008R2, I'll get the records from Person schema where a person's last name is Martinez. The query should be as simple as this:

use AdventureWorks2008R2
go

Select Top 2 t1.FirstName, T1.LastName from Person.Person As T1
where UPPER(T1.LastName) = 'MARTINEZ';
go



The result would be:

FirstName	LastName
Abby	Martinez
Abigail	Martinez

This query returns two names, Abby and Abigail, as both of them have a Martinez surname. However, if the condition should change where I wanted to get people whose surnames were Matthews, all I had to do was change the string constant 'MARTINEZ' to 'MATTHEWS' and I'd get a new result.

use AdventureWorks2008R2
go

Select Top 2 t1.FirstName, T1.LastName from Person.Person As T1
where UPPER(T1.LastName) = 'MATTHEWS';
go

And here's the result:

FirstName	LastName
Joseph	Matthews
Joseph	Matthews

But what if I want to get all persons which have Matthews and Martinez surname from the table? This is where the keyword LIKE comes in. The keyword LIKE in the expression will give us a way to perform searches with pattern matching using wildcards, and in this case, is a percent operator (%) which is padded to the expression to represent any number of characters.

Writing the SQL codes for finding the result of our new query above can be this way. Notice the usage of "Top 5" expression to limit the result to only 5 records:

use AdventureWorks2008R2
go

select Top 5 T1.FirstName, T1.LastName from Person.Person As T1

where Upper(T1.LastName) like 'MAT%'

or Upper(t1.LastName) like 'MAR%'

Order by T1.LastName DESC;

go

... and here's the result:

FirstName	LastName
Joseph	Matthews
Joseph	Matthews
Gigi	Matthew
Zachary	Martinez
Xavier	Martinez

The person named Gigi Matthew also showed up in the result. This is because the expression Where Upper(T1.LastName) Like 'MAT%'  will pull any records whose surnames include "M-A-T" characters in them.

Another way to get the same result is to use a restrictive pattern matching where, instead of just using the percent (%) operator, we're going to add square brackets to our expression.

Let's write the script:

use AdventureWorks2008R2
go

select Top 5 T1.FirstName, T1.LastName from Person.Person As T1

where Upper(T1.LastName) like 'MA[T,R]%'

Order by T1.LastName DESC;

go

Result:

FirstName	LastName
Joseph	Matthews
Joseph	Matthews
Gigi	Matthew
Zachary	Martinez
Xavier	Martinez

As I said earlier, the last query will have the same result as the previous one. The good thing with using square brackets though is that it can limit the extent of the search performed within what is listed inside the square bracket ( [] ).

Here are some of my examples for using the square bracket:

use AdventureWorks2008R2
go

select Top 10 T1.FirstName, T1.LastName from Person.Person As T1
where Upper(T1.LastName) like 'M[A-C]S%'
Order by T1.LastName;
go

Result:

FirstName	LastName
Steve	Masters
Steve	Masters
Steve	Masters
Nancy	McSharry Jensen

The "A-C" in the expression inside the square brackets will limit the pattern matching to any surname whose second character can be any of the letters from A to C. The "S" at the end of the square brackets limits the search to any surname with an S in the third character. A restrictive method is shown in the example below:

use AdventureWorks2008R2

go

select T1.FirstName, T1.LastName from Person.Person As T1
where Upper(T1.LastName) like 'M[^A,^O]S%'
Order by T1.LastName;
go

Result:

FirstName	LastName
Nancy	McSharry Jensen
Tosh	Meston

The last result shows that including the ^ symbol to the list characters of the range characters will not include any person whose surnames have A or O as the second character.