# 6. Using Wildcard Filtering

---[SIDEBAR]---
"type":"objectives",
"title":"Learning Objectives",
"text":"After completing this lesson you can:
  1. Create complex filters using wildcard characters and the keyword LIKE.
  2. Use the wildcard character '%' in place of multiple characters.
  3. Use the wildcard character '_' in place of a single character.
  4. Use the wildcard characters '[]' to match one of a set of characters.
"
---[SIDEBAR]---

## Using the LIKE Operator

In each of the filtering examples you have examined in this resource by compared values in a table to one or more known values. The expression WHERE birth = 1861 compares the values in the birth column to the known value 1861 and returns those rows that meet this condition. Similarly, the statement WHERE birth BETWEEN 1861 AND 1865 compares the values in the birth column to both 1861 and 1865 (known values) and returns those rows where the birth is greater than or equal to 1861 *and* less than or equal to 1865.

There are times when the values you wish to compare are not completely known. When this happens you can use a wildcard to replace missing characters in a filter to create comparisons based on patterns rather than exact values. A pattern contains literal text and one or more wildcard characters that stand in the place of unknown characters.

---[SIDEBAR]---
"type":"term",
"title":"Wildcard",
"text":"A special character used to replace one or more missing characters in a filter."
---[SIDEBAR]---


---[SIDEBAR]---
"type":"term",
"title":"Pattern",
"text":"A filter value that contains literal text and one or more wildcard characters that stand in the place of unknown characters."
---[SIDEBAR]---



For example, you may want to list all of the artists whose style includes the word 'classic'. The word classic could appear anywhere in the style. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list the artists and their style whose style includes the word \"classic\".",
"pretext": "Sure! Here's the SQL statement to list the artists and their style whose style includes the word \"classic\":",
"response": "SELECT full_name, style \nFROM artist \nWHERE style like '%classic%';",
"posttext": "This query selects the full_name and style columns from the artist table. The WHERE clause uses the LIKE operator to search for the word \"classic\" somewhere within the style column. The % symbol is a wildcard that represents any number of characters before or after the word \"classic\". This query will return the names of artists and their corresponding styles where the style includes the word \"classic\" in any position."
}
]
---[AI]---





For example, you may want to list all of the artists whose style includes the word 'classic'. The word classic could appear anywhere in the style. The syntax for a query that accomplishes this is:

*SELECT full_name, style
FROM artist
WHERE style LIKE '%classic%'*



**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas|visible
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, style
FROM artist
WHERE style LIKE '%classic%';
---[SQL]---

Notice the syntax of the WHERE clause. A wildcard filter uses the keyword LIKE to signal to the DMBS that a wildcard filter has been specified. It also contains a pattern to be used for comparison. The pattern in this filter contains two wildcard characters '%' and the literal text classic to return all artists with the word classic somewhere in the style.

---[SIDEBAR]---
"type":"note",
"title":"Wildcards and Data Types",
"text":"Wildcards filters can only be used with columns that have a text data type. They cannot be used with columns of any other data type."
---[SIDEBAR]---

SQL supports more than one wildcard character. Each of them is discussed in the next three sections of this lesson.

## The Percent Sign (%) Wildcard

The most commonly used wildcard character is the % sign. The % sign substitutes for any character or set of characters as you set a pattern for the text you'd like the see in the query results. In the previous example, you used the % sign both before and after the word "classic". This set a pattern for the text to be selecting the the where clause. The % character before the word classic means that any character or set of characters could appear before the word classic in the results. Likewise, the % after the word classic means that any character or set of characters could appear after the word classic. This means that the word classic would need to appear anywhere in the style for an artist to appear in the query result.

---[SIDEBAR]---
"type":"note",
"title":"The % Wildcard",
"text":"The % character substitutes for any character or set of characters including no characters at all. This means that the pattern %classic% in the last example would include those styles with the word classic as the first or last word of the style."
---[SIDEBAR]---




It is helpful to examine a few more examples. Suppose you wish to list the artists whose style starts with the word "art". You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists and their style whose style starts with the word \"art\".",
"pretext": "Certainly! Here's the SQL statement to list the artists and their style whose style starts with the word \"art\":",
"response": "SELECT full_name, style \nFROM artist \nWHERE style like 'art%';",
"posttext": "This query selects the full_name and style columns from the artist table. The WHERE clause uses the LIKE operator to search for the word \"art\" at the beginning of the style column. The % symbol is a wildcard that represents any number of characters after the word \"art\". This query will return the names of artists and their corresponding styles where the style starts with the word \"art\"."
}
]
---[AI]---




It is helpful to examine a few more examples. Suppose you wish to list the artists whose style starts with the word "art". This SELECT statement will do that:

*SELECT full_name, style
FROM artist
WHERE style LIKE 'art%'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, style
FROM artist
WHERE style LIKE 'art%';
---[SQL]---

In this example, the pattern for the text to be selected is "art%". This means that the word "art" must be first in the style, but it can be followed by any character or set of characters. in this case, the style "Art Nouveau" is returned, because it starts with the word "art". Any style that starts with the word "art" would be selected.



Consider another example. Assume you wish to list the artists whose style ends with the word "art". You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists and their style whose style ends with the word \"art\".",
"pretext": "Sure! Here's the SQL statement to list the artists and their style whose style ends with the word \"art\":",
"response": "SELECT full_name, style \nFROM artist \nWHERE style like '%art';",
"posttext": "This query selects the full_name and style columns from the artist table. The WHERE clause uses the LIKE operator to search for the word \"art\" at the end of the style column. The % symbol is a wildcard that represents any number of characters before the word \"art\". This query will return the names of artists and their corresponding styles where the style ends with the word \"art\"."
}
]
---[AI]---




Consider another example. Assume you wish to list the artists whose style ends with the word "art". This SELECT statement will do that:

*SELECT full_name, style
FROM artist
WHERE style LIKE '%art'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, style
FROM artist
WHERE style LIKE '%art';
---[SQL]---

In this case, the pattern is "%art". This will return all artists whose style has the word "art" at the end with any character or set of characters before the word art. This includes the styles of Landscape Art and Modern Art.



In this next example assume you wish to list the artists whose style contains the word "art". You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists and their style whose style contains the word \"art\".",
"pretext": "Certainly! Here's the SQL statement to list the artists and their style whose style contains the word \"art\":",
"response": "SELECT full_name, style \nFROM artist \nWHERE style like '%art%';",
"posttext": "This query selects the full_name and style columns from the artist table. The WHERE clause uses the LIKE operator with % wildcard on both sides of the word \"art\". This allows the query to search for the word \"art\" anywhere within the style column. The query will return the names of artists and their corresponding styles where the style contains the word \"art\" in any position."
}
]
---[AI]---




In this next example assume you wish to list the artists whose style contains the word "art". This SELECT statement will do that:

*SELECT full_name, style
FROM artist
WHERE style LIKE '%art%'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, style
FROM artist
WHERE style LIKE '%art%';
---[SQL]---

This example uses a pattern you used before with the word classic. In this case, the pattern "%art%" will return artists whose style has the word art anywhere in the style.



Consider one final example. Assume you wish to list the artists whose style starts with the letter "a" and ends with the letter "t". You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists and their style whose style starts with \"a\" and ends with \"t\".",
"pretext": "Sure! Here's the SQL statement to list the artists and their style whose style starts with 'a' and ends with 't':",
"response": "SELECT full_name, style \nFROM artist \nWHERE style like 'a%t';",
"posttext": "This query selects the full_name and style columns from the artist table. The WHERE clause uses the LIKE operator with 'a' followed by % wildcard at the beginning, and 't' at the end. This allows the query to search for styles that start with 'a' and end with 't'. The query will return the names of artists and their corresponding styles that meet this condition."
}
]
---[AI]---




Consider one final example. Assume you wish to list the artists whose style starts with the letter "a" and ends with the letter "t". This SELECT statement will do that:

*SELECT full_name, style
FROM artist
WHERE style LIKE 'a%t'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, style
FROM artist
WHERE style LIKE 'a%t';
---[SQL]---

In this case, the pattern "a%t" retrieves the artists whose style starts with the letter "a" and ends with the letter "t". This includes American West and America West.

---[SIDEBAR]---
"type":"caution",
"title":"Wildcards and Unusual Situations",
"text":"There are some interesting unusual cases to be aware of when using wildcard criteria."
---[SIDEBAR]---

## The Underscore (_) Wildcard

The % wildcard character is powerful and useful. The underscore (_) character is also useful, particularly when using a more prescriptive pattern. The underscore character is used to substitute for any single character. It is used in place of only one character (no more and no fewer).



Suppose you wish to list the artists whose last name is similar to "monet". In particular, you want to retrieve those artists whose last name starts with the letters "mo", has any third character, and ends with the letters "et". You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists whose last name starts with the letters 'mo', has any third character, and ends with the letters 'et'.",
"pretext": "To list the artists whose last name starts with the letters 'mo', has any third character, and ends with the letters 'et', you can use the following SQL statement:",
"response": "SELECT full_name \nFROM artist \nWHERE last_name like 'mo_et';",
"posttext": "In this query, the SELECT clause selects the full_name column from the artist table. The WHERE clause uses the LIKE operator with 'mo_et' pattern to search for artists whose last name meets the specified conditions. The underscore _ is a wildcard that matches any single character, so 'mo_et' will match any last name that starts with 'mo', has any third character, and ends with 'et'. The query will return the names of artists whose last names satisfy this pattern."
}
]
---[AI]---




Suppose you wish to list the artists whose last name is similar to "monet". In particular, you want to retrieve those artists whose last name starts with the letters "mo", has any third character, and ends with the letters "et". This SELECT statement will do that:

*SELECT full_name
FROM artist
WHERE last_name LIKE 'mo_et'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name
FROM artist
WHERE last_name LIKE 'mo_et';
---[SQL]---

Notice that the query returns two artists, Claude Monet and Henry Moret. The last name of each artist follows the same pattern. They both start with the letters 'mo', have any third character, and end with the letters 'ot'.



You can also mix and match wildcard characters. Suppose you wish to list the artists whose last name has the letter 'n' as the third letter. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists whose last name contains the letter 'n' as the third letter.",
"pretext": "To list the artists whose last name contains the letter 'n' as the third letter, you can use the following SQL statement with the LIKE operator and underscore _ wildcard:",
"response": "SELECT full_name \nFROM artist \nWHERE last_name like '__n%';",
"posttext": "In this query, the SELECT clause selects the full_name column from the artist table. The WHERE clause uses the LIKE operator with '__n%' pattern to search for artists whose last name contains the letter 'n' as the third letter. The underscore _ is a wildcard that matches any single character, so '__n%' will match any last name with any two characters, followed by 'n' in the third position, and then any number of characters afterward. The query will return the names of artists whose last names satisfy this pattern."
}
]
---[AI]---




You can also mix and match wildcard characters. Suppose you wish to list the artists whose last name has the letter 'n' as the third letter. This SELECT statement will do that:

*SELECT full_name
FROM artist
WHERE last_name LIKE '__n%'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name
FROM artist
WHERE last_name LIKE '__n%';
---[SQL]---

The pattern in this example uses both the "_" and "%" wildcards. The pattern starts with two "_" characters that allow the first two characters to be wildcards. The letter in the third position must be "n". The pattern then allows any character or set of characters after the third position. This pattern matches "Henri" and "Landseer". It would also match "Van Der Heist" and "Man".

## The Brackets ([]) Wildcard



Brackets "[]" are used to specify a set of characters that can appear in a specific position in a pattern. Suppose you want to list the artists whose last name starts with either the letter A or B. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write an SQL statement to list the artists whose last name starts with the letter a or b using the bracket wildcards.",
"pretext": "Sure! You can use the bracket wildcards ([ab]) to list the artists whose last name starts with the letter 'a' or 'b'. Here's the SQL statement:",
"response": "SELECT full_name \nFROM artist \nWHERE last_name like '[ab]%';",
"posttext": "In this query, the SELECT clause selects the full_name column from the artist table. The WHERE clause uses the LIKE operator with the pattern '[ab]%' to search for artists whose last name starts with either 'a' or 'b'. The bracket wildcard [ab] matches any single character that is either 'a' or 'b'. The % symbol is a wildcard that represents any number of characters after the specified letter. The query will return the names of artists whose last names start with either 'a' or 'b'."
}
]
---[AI]---




Brackets "[]" are used to specify a set of characters that can appear in a specific position in a pattern. Suppose you want to list the artists whose last name starts with either the letter A or B. This SELECT statement will do that:

*SELECT full_name
FROM artist
WHERE last_name LIKE '[ab]%'*



**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name
FROM artist
WHERE last_name LIKE '[ab]%';
---[SQL]---

The pattern in this example uses brackets to specify a set of characters (a or b) for the character in the first position of the last name. Any artist whose last name starts with a or b will be retrieved by the query.

---[SIDEBAR]---
"type":"note",
"title":"Bracket Wildcards are often not Supported",
"text":"Many DBMS do not support bracket wildcards. They are supported in Microsoft SQL Server. They are supported in MySQL, Oracle, DB2, or SQLite. Check with your database administrator to determine which DBMS is used in your company."
---[SIDEBAR]---

---[SIDEBAR]---
"type":"tip",
"title":"Using Wildcards",
"text":"Queries that use wildcards in the criteria of the WHERE clause take longer to process than other queries. Keep these general principles in mind:"
---[SIDEBAR]---

## Summary

This lesson described how to use wildcard characters in the WHERE clause to create sophistocated filtering conditions. When using wildcards, you will use the keyword LIKE and a combination of the wildcard characters "%", "_", and "[]". Wildcard searches are powerful and you will likely use them often in your query writing.
person
navigate_before Table of Contents expand_more navigate_next
Loading Page hourglass_empty