SQL Server Wildcards: The 5 wildcards you need to know

You are the manager of a new up-and-coming hardware store in the city of Denver, Colorado.

You are speaking with a new customer who says they own a woodworking business, and enjoy shopping at your store very much. They plan on being a regular customer, which you know will bring in lots of fresh cash for the business.

You say you are very excited to have her as a customer, and that you’ll send her a special 10% off coupon for her next purchase. She says “Thank you very much!“, and you say your goodbyes…..

But OMG. You forgot her name.

She said she has signed up for your email list, but you forgot her name. How are you supposed to look her up if you forgot her freaking name?! You remember it sounded like ‘Sara‘, or maybe ‘Kara‘? You remember her last name was kind of long, and started with a ‘W‘, you think.

Would you know how to look her up with those minor details?

Wildcard characters are used in SQL Server to specifically look for character strings that match a specific pattern. In this tutorial, we’ll learn the 5 wildcard characters you can use to find string values within your data.

We’ll learn how we can find our new customer using just the most basic information we have.

Wildcards are part of the Top 6 tools you should know for writing AWESOME Select statements.

If you want to learn more about other extremely common and useful query tools, check out that tutorial.

In this tutorial, we will cover these topics:

1. Using the LIKE keyword
2. Percent (%) wildcard
3. Underscore (_) wildcard
4. [List of characters] wildcard
5. [Range of characters] wildcard
6.  Not [list or range of characters]
7. How to escape a wildcard character
8. Tips, tricks, and links

Starting from the top:

1. Using the LIKE keyword

When we use any of our wildcard characters to find a string value that matches a certain pattern, we need to use it in combination with the ‘LIKE‘ clause.

Normally if you are trying to filter values using a WHERE clause, the template would look something like this:

SELECT * FROM Table WHERE Column = '<value>'

But when using wildcard characters, we can’t use the ‘=’ sign. If you think about it, this sort-of makes sense. When using wildcards, you aren’t looking for a data that is exactly equal to your value. Instead, you are looking for data that is similar to your value.

So when using wildcards, the template looks more like this:

SELECT * FROM Table WHERE Column LIKE '<value with wildcards>'

So of course, in the ‘<value with wildcards>‘ part, you’ll have the wildcard characters you want to use.

Let’s look at some examples.

2. Percent (%) wildcard

The percent wildcard is used when you simply want to look for any number of characters, including zero characters.

As an example, let’s say we remember our customer had a last name that started with the letter ‘W‘. We don’t know any characters after that, but we know it started with that letter.

Using ‘%‘ in our LIKE clause, we can easily locate all last names that start with a ‘W‘, followed by any number of characters:

So this query very plainly shows us last names starting with ‘W‘, and ending with anything.

Pretty straightforward.

3. Underscore (_) wildcard

The underscore wildcard is used when you want to replace a single character with any value.

For example, say you are looking for a customer who’s last name is ‘Gray‘…

Or was it ‘Grey‘ with an ‘e‘? I don’t remember!

When we write our WHERE clause, we can use the underscore wildcard to look for last names with any single character as the third letter, basically like so:

Looks like it was ‘Grey‘ with an ‘e‘ after all.

Remember, the underscore will replace a single character with any value. If we had someone with the last name ‘Gravy‘, the query above would NOT find them.

Also, understand the “single” character can’t be nothing. There needs to be something in that space.

Take the name ‘Sara‘ for example. Some “Sara’s” spell their name with an ‘h‘ at the end, like ‘Sarah‘

If you wanted to look for anyone with the first name ‘Sara‘, who might spell it with an ‘h‘ at the end, you would need to do this:

The ‘%‘ wildcard will look for any number of characters, including zero characters.

The ‘_‘ wildcard will look for any single character, but there needs to be SOMETHING in the space.

So this query returns nobody:

The underscore wildcard will look for any character in the space, which does NOT include NO character in the space. Hopefully that’s not too much of a double negative for you.

4. [List of characters] wildcard

The [list of characters] wildcard is similar to the underscore.

With the underscore, remember, it will return any single character.

If you don’t want to look for just any ol’ character, and instead, want to look for specific characters, there is a way to do that.

If we know the name was either ‘Gray‘ spelled with an ‘a‘ or ‘Grey‘ spelled with an ‘e‘, we can change our query slightly to look for just the letters ‘a‘ or ‘e‘ in the place of the third letter.

Here’s an example:

The query will look for any last names with either ‘a’ OR ‘e’ as the third letter.

In brackets, you list the characters you want to look for in that single space.

You can list as many characters as you want. Let’s add an ‘o‘ to the list in case the person’s last name was actually ‘Groy‘:

Nope, looks like it was ‘Grey‘ after all.

5. [Range of characters] wildcard

The [range of characters] wildcard is similar yet again. Instead of outlining the actual characters, you would outline what range of characters you want to look for, from first to last.

For example, what if you were looking for a person who’s last name sounded like ‘-otter‘

Maybe it was ‘Cotter‘, or ‘Potter‘, or ‘Gotter‘, or maybe even ‘Lotter‘.

Let’s see how this query would look if we used the [list of characters] wildcard that we learned about in our last point. We’ll outline all letters between ‘C‘ and ‘P‘:

Cool, but that was the hard way.

If we wanted to outline all letters between ‘C‘ and ‘P‘, we can use the [range of characters] wildcard:

You just outline what range of characters or numbers you’re looking for in brackets using a dash in between the first and last character.

And again, it will only apply to a character in a single space.

6. Not [list or range of characters]

You can basically negate the [list of characters] or [range of characters] wildcards.

Let’s think of an example. Say you’re looking for someone whose first name sounds like ‘-ara‘.

You’re pretty sure it was either ‘Sara‘ or ‘Kara‘. And you’re sure it definitely wasn’t ‘Tara‘.

If you were doing the [list of characters] wildcard, you could do the following:

This works fine, but if you know the name definitely wasn’t ‘Tara‘, you could choose to do the following instead:

You put a ‘^‘ in front of your list of characters. This says to look for any single character that is specifically NOT the list of characters you have outlined.

So in our example, we will look for any name that ends in ‘-ara‘ where the first letter specifically is NOT ‘T‘.

Of course, I could list several characters if I wanted to:

This would look for any name that ends in ‘-ara‘ where the first letter is specifically not ‘T‘, ‘P‘, ‘R‘, or ‘M‘.

I could do the same thing with the [range of characters] wildcard:

So this would look for names where the first letter is specifically not in the range of ‘A‘ through ‘J‘ in the alphabet.

7. How to escape a wildcard character

When looking for specific data, sometimes you want to look for a character that is used as a wildcard character in SQL. In other words, you don’t want the wildcard character to be treated as a wildcard character.

Here is an example. Say you have a table called ‘BookCollection‘, and you want to look for a book that had something to do with the 20% rule.

If you’re unfamiliar with the 20% rule, it basically means 20% of our effort determines 80% of our results. It’s a good plan to live by.

But I digress. You don’t remember the full name of the book, but you remember it had the phrase ‘20%‘ in it.

You might try the following query:

This returns more books than you expected. Why is it showing us books that don’t have ‘20%‘ in the title?

Because remember, the ‘%‘ symbol is a wildcard. That second ‘%‘ symbol is being used as a wildcard and not as an actual percent character.

If you wanted to look for the actual percent character, you would need to escape it. It’s easy to do:

You wrap brackets around the character you want to be seen as an actual character, and not as a wildcard character.

In other words, the query above will look for books where the title has:

<any number of characters>20% <any number of characters>

You can use brackets to escape any of the characters used for wildcards, including ‘%‘, ‘_‘, ‘[‘, or ‘]‘ characters (yes, it would look weird if you want to escape brackets using brackets, but it works!).

There is another way to escape wildcard characters I will tell you about, but I honestly think it’s more work.

You could have written the query above like this instead:

You can use the ESCAPE keyword.

The way it works is you create your own custom escape character. In my example, I chose to use the ‘*‘ symbol. You identify what your ESCAPE character is after the LIKE clause.

In the LIKE clause, you would put your custom escape character in front of whatever wildcard character you are wanting to escape.

So in my example, I put the ‘*‘ symbol in front of the second ‘%‘ symbol.

Doesn’t that seem like more work? I suppose it has it’s time and place, so it’s good to be aware of.

8. Tips, tricks, and links

Here are a few tips and tricks you should know about the wildcard characters:

Tip # 1: Numbers are characters, too

In our examples, we have used mostly letters in our [list of characters] and [range of characters] wildcards. You could also use numbers if you needed to (remember, even though it’s a number, it’s still a character).

Like if you couldn’t remember if the name of the book was ‘2000 Leagues Under the Sea‘ or if it was ‘3000 Leagues Under the Sea‘, or maybe it was even ‘4000 Leagues Under the Sea‘, you could do this:

I just don’t want you thinking the wildcard characters will only work with letters. It will work with any characters.

Tip # 2: You can use more than one wildcard character in your LIKE clause.

Remember at the start of this tutorial where we were talking to someone named either ‘Sara‘ or ‘Kara‘, and her last name started with a ‘W‘.

We already discussed how some “Sara’s” spell their name with an ‘h‘ at the end, like ‘Sarah‘. You’ll want to write your query to make sure that get’s picked up just in case.

We can write a query that uses both the [list of characters] wildcard and the ‘%‘ wildcard in one LIKE clause:

I think we can say with confidence the person we were speaking with was Sara Winchester!

Links

There is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including a discussion about wildcards. This is one of only a few books I own that helped me understand many SQL Server topics. You won’t regret owning this book, trust me. Definitely get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

Are you familiar with these 6 tools you should know for writing AWESOME Select statements? Check out the tutorial to find out!

Another great topic you should learn as a beginner is everything to do with NULL. It’s important to know what NULL is and how it is used in SQL Server. Check out the full tutorial here:

Are you making these 7 mistakes with NULL?

Finally, you should download this FREE guide to learn and understand the most common data types you will encounter as a database professional:

FREE guide on the Top 10 data types you should know!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!