---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to:
  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.
" ---[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. ---[EVALUATE]--- "type":"plan", "task":"List all of the artists whose style includes the word 'classic'.", "columns":"full_name, style", "tables":"artist", "rows":"Style includes the word classic", "order":"none specified" ---[EVALUATE]---
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]---
The syntax for a query that accomplishes this is: SELECT full_name, style FROM artist WHERE style like '%classic%';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, style \nFROM artist \nWHERE style like '%classic%';" ---[EVALUATE]--- The query will retrieve the correct rows. The full_name and style columns will be returned from the artist table. 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. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas|visible owner:atlas-query result:artist-classic-in-style ---[SQL]--- SELECT full_name, style FROM artist WHERE style LIKE '%classic%'; ---[SQL]--- The query produces a list of artists. Each of the artists have the word classic somewhere in the style column. Since some DBMS are case sensitive when comparing values, those styles with the word "Classic" (note the capital C) will not be returned. ---[SIDEBAR]--- "type":"note", "title":"Case sensitivity", "text":"Oracle DB uses case sensitivity when comparing values while other DBMS, like SQLite does not. In the prior example, a query of an Oracle database will not return Artists with the style 'Classicist' while SQLite will return these artists." ---[SIDEBAR]--- SQL supports more than one wildcard character. Each of them is discussed in the next three sections of this lesson. ---[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]--- ## 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". ---[EVALUATE]--- "type":"plan", "task":"List the artists whose style starts with the word Art.", "columns":"full_name, style", "tables":"artist", "rows":"Style starts with the word Art", "order":"none specified" ---[EVALUATE]---
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]---
This SELECT statement will do that: SELECT full_name, style FROM artist WHERE style like 'Art%';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, style \nFROM artist \nWHERE style like 'Art%';" ---[EVALUATE]--- The SQL statement retrieves the correct rows. It returns the full_name and style columns from the artist table. 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. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-art-begins-style ---[SQL]--- SELECT full_name, style FROM artist WHERE style LIKE 'Art%'; ---[SQL]--- ---[SIDEBAR]--- "type":"note", "title":"Case sensitivity", "text":"In this example it is important to capitalize the letter A if you are using a DBMS that is case sensative in value comparisons. If your DBMS is not case sensative (like SQLite) in value comparisons either the syntax LIKE 'Art%' or LIKE 'art%' will return the same results." ---[SIDEBAR]--- 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 letters "art". ---[EVALUATE]--- "type":"plan", "task":"List the artists whose style ends with the letters art.", "columns":"full_name, style", "tables":"artist", "rows":"Style ends with the letters art.", "order":"none specified" ---[EVALUATE]---
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]---
This SELECT statement will do that: SELECT full_name, style FROM artist WHERE style like '%art';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, style \nFROM artist \nWHERE style like '%art';" ---[EVALUATE]--- This SQL statement will retrieve the correct results. It will return the full_name and style columns from the artist table. In this case, the pattern used in the WHERE clause 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. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-art-ends-style ---[SQL]--- SELECT full_name, style FROM artist WHERE style LIKE '%art'; ---[SQL]--- Notice the results of the query. The results include artists with styles of Landscape Art and Modern Art. All of the artists returned include the letters art at the end. In this next example assume you wish to list the artists whose style contains the word "art". ---[EVALUATE]--- "type":"plan", "task":"List the artists whose style contains the letters art.", "columns":"full_name, style", "tables":"artist", "rows":"Style contains the letters art.", "order":"none specified" ---[EVALUATE]---
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%';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, style \nFROM artist \nWHERE style like '%art%';" ---[EVALUATE]--- This query will return the correct results. 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. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-art-in-style ---[SQL]--- SELECT full_name, style FROM artist WHERE style LIKE '%art%'; ---[SQL]--- Notice the results of the query. Artists with the consecutive letters art anywhere in the style will be included in the query results. Consider one final example. Assume you wish to list the artists whose style starts with the letter "a" and ends with the letter "t". ---[EVALUATE]--- "type":"plan", "task":"List the artists whose style starts with the letter a and ends with the letter t.", "columns":"full_name, style", "tables":"artist", "rows":"Style starts with the letter a and ends with the letter t.", "order":"none specified" ---[EVALUATE]---
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';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, style \nFROM artist \nWHERE style like 'a%t';" ---[EVALUATE]--- The query will return the correct records. It will retrieve the full_name and style columns from the artist table. The pattern in the where clause "a%t" retrieves the artists whose style starts with the letter "a" and ends with the letter "t". **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-style-a-begins-t-ends ---[SQL]--- SELECT full_name, style FROM artist WHERE style LIKE 'a%t'; ---[SQL]--- The results of this query include styles like American West and America West. These start with the letter A and end with the letter T. Notice that the results don't contain words that begin with A and end with T. The entire style must end with the letter A and end with the letter T. ---[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". ---[EVALUATE]--- "type":"plan", "task":"List the artists whose last name starts with the letters mo, has any third character, and ends with the letters et.", "columns":"full_name", "tables":"artist", "rows":"Last name starts with the letters mo, has any character in the third position, and ends with the letters et.", "order":"none specified" ---[EVALUATE]---
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';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name \nFROM artist \nWHERE last_name like 'mo_et';" ---[EVALUATE]--- This SQL statement will retrieve the correct results. It will return the full_name column from the artist table. The WHERE clause uses the underscore "_" character to create the pattern mo_et. This will return artists whose last name starts with the letters mo, has any third character, and ends with the letters et. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-lastname-mo-begins-et-ends ---[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. ---[EVALUATE]--- "type":"plan", "task":"List the artists whose last name has the letter 'n' as the third letter.", "columns":"full_name", "tables":"artist", "rows":"The last name has the letter n as the third letter.", "order":"none specified" ---[EVALUATE]---
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]---
This SELECT statement will do that: SELECT full_name FROM artist WHERE last_name like '__n%';
---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name \nFROM artist \nWHERE last_name like '__n%';" ---[EVALUATE]--- The SQL statement retrieves the correct results. It projects the full_name column from the artist table. The WHERE clause uses uses both the "_" and "%" wildcards. The pattern created in the WHERE clause starts with two "_" characters that allow the first two characters of the last name 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. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-lastname-n-third-letter ---[SQL]--- SELECT full_name FROM artist WHERE last_name LIKE '__n%'; ---[SQL]--- Examine the results of the query. The wildcard pattern in the WHERE clause matches "Henri" and "Landseer". It would also match "Van Der Heist" and "Man". All of these names have the letter n as the third character. ## 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.