SQL Server Using LIKE with Wildcards

LIKE is a powerful keyword used along with the WHERE clause to lookup and retrieve data from your database tables. LIKE is used to find patterns using wildcard characters. Here are a bunch of wildcard characters that you can use with LIKE:

% (Percentage) – a string of zero or more characters
_ (Underscore) – a single character
[] (Square brackets) – a single character within a range

Let us explore some scenarios of using these wildcards with the LIKE operator. We will be using the free AdventureWorks database and the Department Table within this database.

Assuming you have downloaded and installed AdventureWorks, let us try out some commands on the Department table and the Name column:

Departments starting with letter 'P'

SELECT * from HumanResources.Department
WHERE Name LIKE 'p%';

As you can see, we have a list of all department names starting with the letter ‘P’

Departments starting with letters 'Pro'

Similarly you can use multiple letters with %. So for departments starting with letter ‘Pro’, use this T-SQL

SELECT * from HumanResources.Department
WHERE Name LIKE 'pro%';

Department starting with ‘P’ followed by any character but with 'O' as the third character

Here’s where we use the underscore operator _

SELECT * from HumanResources.Department
WHERE Name LIKE 'p_o%';

Departments starting with E, M or P

To list all departments starting with either e, m or p, we can use the square bracket [] wildcard

SELECT * from HumanResources.Department
WHERE Name LIKE '[emp]%';

Departments starting in the range [M to Q]

To list all departments that start with any character from M to Q, use the following:

SELECT * from HumanResources.Department
WHERE Name LIKE '[m-q]%';

Departments NOT starting in the range [M to Q]

Similarly, you can use the not operator ^ to list all departments that do not start with letters falling between M to Q

SELECT * from HumanResources.Department
WHERE Name LIKE '[^m-q]%';

There is another way of writing this query using the NOT LIKE keyword

SELECT * from HumanResources.Department
WHERE Name NOT LIKE '[m-q]%';

and the output will be the same as above

Departments ending with letter 'G'

So far we have used the % operator to denote words starting with a letter. To denote words ending with a letter, just precede the % wildcard before the letter.

SELECT * from HumanResources.Department
WHERE Name LIKE '%g';

Department containing a pattern 'ing'

You can even use patterns if you are not sure. So for example, you want to list all words which end with a pattern ‘ing’

SELECT * from HumanResources.Department
WHERE Name LIKE '%ing%';

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook