It is time for us to learn about wildcards in SQL. Here is what we will learn today by reading through this post.
Learning Objectives:
- Explain the concepts of wildcards.
- Wildcard advantages and disadvantages.
- Describe how to use the LIKE operator in wildcards.
- Write appropriate syntax using wildcards.
What are Wildcards?
- Can only be used with strings.
- Cannot be used for non-text datatypes.
- Helpful for data scientists as they explore string variables.
Using % Wildcards.
Here are three ways we can use wildcards.
‘%Charizard’ evaluates anything ending with the word Charizard.
‘Charizard%’ evaluates anything after the word Charizard.
‘%Charizard%’ evaluates anything before and after the word Charizard.
We could also be more specific with the way we use wildcards for example people often confuse the spelling of my name. However, two parts of my name they never get wrong are the beginning and end. We could use wildcards to help query my name from a table.
'M%D'This would evaluate anything that starts with ‘M‘ ’and ends with ‘D’.
We could also do the same if we know the start of the data point but do not know the rest.
'ch%@gmail.com'This would return to all Gmail addresses starting with ‘ch’.
Important to note that wildcards cannot be used with NULL values since they don’t actually have any data or value to match up with.
Underscore (_) Wildcard.
We can match a single character using ‘_’.
WHERE size LIKE '_pizza'
Output:
spizza
mpizzaThis syntax is not supported by some database systems. You should make sure to understand the difference between database management systems so that you can correctly query the data it holds.
Downsides of Wildcards.
- Take longer to run.
- Better to use another operator if possible.
- Statements with wildcards take longer to run if at the end of a search pattern.
- placement of wildcards is important.
