8 Using Wildcard Filtering

8.1 Using the LIKE Operator

In each of the filtering examples you have examined in this resource, you 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.

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

Notice the syntax of the WHERE clause. A wildcard filter uses the keyword LIKE to signal to the DBMS 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.

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

8.2 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 to 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 selected in 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.

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

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.

8.3 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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT full_name
FROM artist
WHERE last_name LIKE 'mo_et';

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 'et'.

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 an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

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

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".

8.4 Matching a Set of Characters with SIMILAR TO

Some DBMSs let you use square brackets — [] — directly inside a LIKE pattern to match a set of characters at a single position. PostgreSQL's LIKE does not support this: brackets in a LIKE pattern are treated as ordinary, literal characters, not as a wildcard. To get this behavior in PostgreSQL, you use a different operator: SIMILAR TO.

SIMILAR TO combines pattern matching with SQL regular expression syntax, including bracket expressions. Suppose you want to list the artists whose last name starts with either the letter A or B. You can use a chat with an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT full_name
FROM artist
WHERE last_name SIMILAR TO '[ab]%';

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. Notice that the % wildcard still works the same way inside a SIMILAR TO pattern as it does with LIKE.

8.5 Summary

This lesson described how to use wildcard characters in the WHERE clause to create sophisticated filtering conditions. When using wildcards with LIKE, you will use a combination of the wildcard characters "%" and "_". PostgreSQL's LIKE does not support bracket expressions the way some other DBMSs do, so matching a set of characters at a position requires the SIMILAR TO operator (or, for full regular expressions, the ~ operator) instead. Wildcard and pattern-matching searches are powerful and you will likely use them often in your query writing.