You can use the %
and _
wildcards with the SQL LIKE statement to compare values from an SQL table. But how does that work exactly?
In this article, I will show you how to use the SQL LIKE statement through code examples.
Basic syntax of SQL LIKE statement
Here is the basic syntax for the SQL Like statement:
SELECT FROM table_name
WHERE column LIKE 'string pattern'
pattern matching enables you to use_
to match any single character and%
to match an arbitrary number of characters (including zero characters)
For example, if we wanted to find all names in the table that started with the letter "T" then we could use this syntax:
WHERE name LIKE 'T%'
Or if we wanted to find all names in the table that contained the letters "on", then we could use this syntax:
WHERE name LIKE '%on%'
We can use the _
wildcard to find a single character match. For example, this is the syntax to find all numbers in the quantity
category that are 2 digits long and end with '9':
WHERE quantity LIKE '_9';
To better understand how these wildcards work with the SQL Like statement, let's take a look at an example table of data.
How to use the SQL LIKE statement – example with a cars table
In this example, we have a cars
table with the columns of id
, model
, make
and price
.
id|make|model|price
1|Honda|Civic|21000
2|Ford|Fusion|23000
3|Toyota|Camry|24000
4|Dodge|Challenger|29000
5|Tesla|Model X|104000
6|Chevrolet|Tahoe|49000
How to use the %
Wildcard with the SQL LIKE statement
In this first example, we want to find all car models that start with the letter "C".
SELECT * FROM cars
WHERE model LIKE 'C%';
This code would return the following results from the cars
table:
id|make|model|price
1|Honda|Civic|21000
3|Toyota|Camry|24000
4|Dodge|Challenger|29000
We can see that 3 out of the 6 entries from our cars
table have model names that start with the letter "C".
The SQL LIKE statement is not case sensitive which means 'C%'
and 'c%'
would return identical results.
We can also use the %
wildcard and SQL LIKE statement to find entries that end with a character or characters.
In this example, we want to find all of the car makers whose name ends with an "a".
SELECT * FROM cars
WHERE make LIKE '%a';
This code would return the following results from the cars
table:
id|make|model|price
1|Honda|Civic|21000
3|Toyota|Camry|24000
5|Tesla|Model X|104000
We can see that 3 out of the 6 car makers have a name that ends with the letter "a".
How to use multiple %
Wildcards with the SQL LIKE statement
In this example, we want to find all car prices that include the number 9 in them.
SELECT * FROM cars
WHERE price LIKE '%9%';
This code would return the following results from the cars
table:
id|make|model|price
4|Dodge|Challenger|29000
6|Chevrolet|Tahoe|49000
We can see that 2 out of the 6 car prices include the number 9.
How to use the _
Wildcard with the SQL LIKE statement
We can use the _
wildcard to find a single character match.
Let's modify our cars
table:
id|make|model|price
30|Honda|Civic|21000
35|Ford|Fusion|23000
40|Toyota|Camry|24000
45|Dodge|Challenger|29000
50|Tesla|Model X|104000
55|Chevrolet|Tahoe|49000
In this example, we want to find all ids that are two digits long and end in the number 0.
SELECT * FROM cars
WHERE id LIKE '_0';
This code would return the following results from the cars
table:
id|make|model|price
30|Honda|Civic|21000
40|Toyota|Camry|24000
50|Tesla|Model X|104000
We can see that 3 out of the 6 two-digit car ids end in the number 0.
How to use multiple _
Wildcards with the SQL LIKE statement
Let's modify our cars
table again.
id|make|model|price
130|Honda|Civic|21000
135|Ford|Fusion|23000
140|Toyota|Camry|24000
145|Dodge|Challenger|29000
150|Tesla|Model X|104000
155|Chevrolet|Tahoe|49000
In this example, we want to find all ids that are three digits long and end in the number 0.
SELECT * FROM cars
WHERE id LIKE '__0';
This code would return the following results from the cars
table:
id|make|model|price
130|Honda|Civic|21000
140|Toyota|Camry|24000
150|Tesla|Model X|104000
We had to use two underscores _
with the SQL LIKE statement to find all ids that are three digits long and end in the number 0.
Using two underscores
'__0'
instead of just one
'_0'
How to use the NOT Operator with the SQL LIKE statement
We can use the NOT operator in SQL to find all results that do not match the string pattern in the LIKE statement.
We can modify our last example to find all three digit ids that do not end in the number 0.
SELECT * FROM cars
WHERE id NOT LIKE '__0';
This code would return the following results from the cars
table:
id|make|model|price
135|Ford|Fusion|23000
145|Dodge|Challenger|29000
155|Chevrolet|Tahoe|49000
How to use the %
and _
Wildcards with the SQL LIKE statement
In this example, we want to find all car makers whose second letter is an "o" and the name ends with an "a".
SELECT * FROM cars
WHERE make LIKE '_o%a';
The _o
is to find all car makers whose second letter is "o". The %a
is to find all car makers that end with the letter "a".
This code would return the following results from the cars
table:
id|make|model|price
130|Honda|Civic|21000
140|Toyota|Camry|24000
Conclusion
You can use the %
and _
wildcards with the SQL LIKE statement to compare values from a SQL table.
Here is the basic syntax for the SQL Like statement.
SELECT FROM table_name
WHERE column LIKE 'string pattern'
The %
matches zero, one or more characters while the _
matches a single character.
In this article, we learned how to use both of these wildcards with the SQL LIKE statement using the cars
table example.
I hoped you enjoyed this article and best of luck on your SQL journey.