Share via


Wildcard Characters

You can search for patterns within data columns or expressions by using wildcard characters. For example, you can search for all employees whose last names begin with "Van" or ends with "son."

Wildcard Characters

You can use wildcard characters to search any columns that can be treated as text strings. Columns with the data type character can always be treated as text strings; those with datetime data can be treated as text strings for some databases.

To search for patterns, use the LIKE operator, and then substitute wildcard characters for one or more characters in the search string. You can use either of the following wildcard characters:

Wildcard character

Meaning

% (percent symbol)

Zero or more characters in that position.

_ (underscore)

One character at that position.

For example, to search for all names beginning with "Van," you could specify the search condition LIKE 'Van%'. To find names such as "Jan," "Jen," and "Jon," you could specify the search condition LIKE 'J_n'.

Searching for Characters Used as Wildcard Characters

In some cases, you might need to search for a string that contains one of the characters used as a wildcard character. For example, in a titles table you might want to find all the publications that contain the string "10%" as part of the title. Because "%" is part of the string you are searching for, you must specify that you mean it as a literal string and not a wildcard character.

To search for characters that can be interpreted as wildcard characters, you can specify an escape character. Place the escape character immediately in front of the "%" or "_" character that you mean literally. To specify the escape character, include an ESCAPE clause immediately after the LIKE search criterion. You can do this in either the Criteria column of the Criteria Pane or in the SQL pane.

For example, imagine that you want to find all titles that contain the string "10%." Suppose you want to define the character "#" as an escape character, which allows you to include "#" in front of the "%" character that is meant literally. You can enter this in Criteria Pane:

LIKE '%10#%%' ESCAPE '#'

The resulting WHERE clause in the SQL statement looks like this:

WHERE title LIKE '%10#%%' ESCAPE '#'

Note

You can define an escape character only in the SQL pane.

Searching Datetime Columns

When working with a datetime data type column, you can search any portion of the date or time, including text abbreviations of the month and complete years.

Note

The ability to search datetime columns depends on the database you are using and whether datetime data types can be searched as strings. For details, see the documentation for your database.

For example, in a Microsoft SQL Server database, you can use the following LIKE clause to search for all rows in which the date falls within 1994:

LIKE '%1994%'

The following searches for all rows in which the date falls within the month of January, regardless of year:

LIKE 'Jan%'

The exact format of the strings you can use to search a datetime column depends on the database you are using.

Note

If the regional settings of the computer with the database are different than the ones of the computer running the query, you may get unexpected results. To avoid this use the date format expression of the computer with the database. See your database management system documentation for details.

Examples of Wildcard Searches

The following examples illustrate the use of wildcard characters.

Search expression

Description

Sample matches

LIKE 'Van%'

Finds values beginning with "Van."

Vann, Van Beeck, Vanderhorn

LIKE 'J%n'

Finds values starting with "J" and ending with "n."

Jon, Johnson, Jason, Juan

LIKE '%son'

Finds values ending with "son."

Son, Anderson

LIKE '%sam%'

Finds values with "sam" anywhere in the string.

Sam, Samson, Grossam

LIKE '%Mar%'

Finds values in a datetime column that fall in the month of March, regardless of year.

3/1/94 01 Mar 1992

LIKE '%1994%'

Finds values in a datetime column for the year 1994.

12/1/94 01 Jan 1994

LIKE 'Mac_'

Finds values with exactly four characters, the first three being "Mac."

Mack, Macs

LIKE '_dam'

Finds values with exactly four characters, the last three being "dam."

Adam, Odam

LIKE '%s_n'

Finds values containing "s" and "n" at the end of the value, with any one character between them and any number of characters in front of them.

Anderson, Andersen, Johnson, san, sun

See Also

Other Resources

Designing Queries and Views

Reference (Visual Database Tools)