Back to articles list July 31, 2018 - 4 minutes read How to Use a SQL Wildcard Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: data analysis get started how to in sql SQL basics SQL wildcard SQL wildcard allows us to filter data matching certain patterns in SQL. We use SQL wildcards with the LIKE operator in the WHERE clause of a query to filter data. In this beginner’s article, we’ll look at everything you need to know about basic SQL wildcards. If you like playing cards, then you know that wildcards can substitute any other card in your deck. Similarly, SQL wildcards can substitute one or more characters. To start with our SQL wildcards, let’s look at a simple customers table for our pet store: SQL Wildcard with the LIKE Operator Imagine you witness a customer leaving a pet store without paying for his order. These things happen—he just forgot. You know that the seller in the pet store has a database of customers, and you ask him if he knows the name of the customer so you can extract the address and follow up with him. But the seller is not sure about the customer’s name. He knows that the name of the client ends with two letters, os, and that it was short, about only three letters. Maybe it was something like Tos, Los, Kos etc. Using the power of a SQL wildcard, you can handle one ambiguous letter in their database. To represent one character, we use the underscore SQL wildcard. Our SELECT query looks like this: SELECT * FROM customers WHERE surname LIKE ‘_os’; Our query now returns all of our surnames, and we can skip writing the statement for every word combination: SELECT * FROM customers WHERE surname IN (‘Kos’,’Tos’,’Los’); Note that the list of surnames above is by no means exhaustive because the underscore wildcard replaces any character—even one that’s not a letter! Caution: Using SQL Wildcards Without LIKE Operator SQL wildcards only work in the LIKE operator. If you place a wildcard inside an ordinary string that’s not an argument to the LIKE operator, you’ll find that SQL will treat that wildcard as a literal character appearing in the string. For example, consider this alternative query that doesn’t use the LIKE operator: SELECT * FROM customers WHERE surname = ‘_os’; This query would search all surnames that literally equal ‘_os’, and you can bet there are no such records in our table. This is a typical novice mistake, so be careful when using SQL wildcards. Inverting SQL Wildcard Filters To get the inverse of a SQL wildcard filter, such as to find all customers whose last names are not Los, Tos, Kos, etc., you simply apply a negation, NOT, to the LIKE operator: SELECT * FROM customers WHERE surname NOT LIKE ‘_os’; Using SQL Wildcards to Represent a Collection of Characters The underscore wildcard is not the only one available in SQL. A more commonly used SQL wildcard is the percent sign (%), which is used to represent one or more characters. So if we’d like to list all customers who live in German cities that end in ‘burg’, we’d write the following query: SELECT * FROM customers WHERE city LIKE ‘%burg’; This query will return all records for customers whose city of residence is like Hamburg, Augsburg, Oldenburg, Duisburg, and others. Just as we negated the underscore SQL wildcard, we can also negate the percentage SQL wildcard. So if we’d like to extract records for customers whose city of residence does not end in ‘burg’, we’d write: SELECT * FROM customers WHERE city NOT LIKE ‘%burg’; Note: Avoid using the percentage wildcard at the start of a string with the LIKE operator, if possible. This construction is very expensive, as the database needs to evaluate every combination of strings matching that ending pattern. The usage of % after some characters is less expensive in terms of computer resources, as the database already knows the space of strings that it needs to evaluate. Combining Wildcards in LIKE Statements It’s important to note that you can combine SQL wildcards. If you’d like to search for all customers whose city of residence starts with a ‘W’, ends in ‘burg’, and has at least one letter between the ‘W’ and ‘burg’, you can write: SELECT * FROM customers WHERE city NOT LIKE ‘W_%burg’; Go Wild with SQL Wildcards In this article, we looked at how to use SQL wildcards to filter tables. We only covered the basics here. To learn more about SQL wildcards, I highly recommend that you learn about regular expressions. But be wary, because there’s a saying in computer science: If you try to solve a problem using regular expressions, you’ll end up with two problems. Regular expressions are an advanced topic, so have some experience under your belt before you try to master them. Check out this small lesson from LearnSQL.com on wildcards to gain more experience. Tags: data analysis get started how to in sql SQL basics SQL wildcard You may also like How to Begin Running SQL Queries What do you need to run SQL queries? Jump right in to find out! We'll assist you in writing and executing your first SQL query. Read more SQL Indexing 101 Indexes are one of the most misused and misunderstood entities in physical database design. In this article, we'll look at basic database indexes and their role in database development. Read more Our Picks for 2020’s 7 Best Online SQL Schools Looking for the best online SQL courses but aren’t sure where to start? I evaluated the top 7 ranked online SQL schools to help you find your match. Read more SQL Filtering 101 Sometimes, SQL queries return lots of data you don't need. Look at a simple example of filtering SQL queries to reduce the complexity of your data. Read more Why Learn SQL Over the Summer For the love of SQL, we decided to motivate you (yes, you) to learn SQL over the summer. Here's why! Read more Useful SQL Patterns: Pivoting The concept of the pivot in SQL refers to taking the data in table rows and making that data into columns. It’s one of the most useful SQL patterns. Read more Useful SQL Patterns: Date Generator Do you have to manually add all the missing days? No. You can use the SQL pattern known as a date generator to fill in the gaps. Read more Introducing SQL Set Operators: Union, Union All, Minus, and Intersect Ever heard about union and intersection in SQL? They're set operators that come in handy when you need to combine information from multiple tables. Read more A Non-Technical Introduction to Learning SQL on Your Lunch Break Do you think learning SQL will help you in your career? You are right. SQL is one of the easiest computer languages to learn. These days many non-IT employees have SQL skills and use them to extend their professional capacity. Moreover, more and more companies are encouraging their employees in non-IT areas (like sales, advertising, and finances) to learn and use SQL. Read more What's the Difference Between SQL and T-SQL? If you’re a SQL newbie and are confused by the difference between SQL and T-SQL, this article will help make things clear. Read more What SQL Practice Do You Need to Prepare for a Technical Job Interview? Need some SQL practice before a technical job interview? Here are six ways to prepare for various types of database and SQL jobs! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.