Wildcard characters in SQL statements

Statement

Wildcard characters:

The wildcard Description example% contains any string of 0 or more characters. WHERE title like '%computer% ' will find all titles that contain the word computer anywhere in the title. _ (underline) any single character. WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN. [] Any single character in the specified range ([a-f]) or collection ([abcdef]). WHERE au_lname like ' [C-p]arsen ' will look for author surnames that begin with Arsen ending with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on. [^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef]). WHERE au_lname like ' de[^l]% ' will look for the last names of all authors who started with de and whose letter is not followed by L.

Use wildcard characters as text

You can use a wildcard pattern matching string as a literal string by placing the wildcard characters in parentheses. The following table shows examples of using the LIKE keyword and [] wildcard characters.

Symbolic meaning like ' 5[%] ' 5%like ' [_]n ' _nlike ' [A-CDF] ' A, B, C, D or flike ' [-ACDF] '-, A, C, D or flike ' [[] ' [like '] ']like ' abc[_]d% ' abc_d and Abc_delike ' abc[def] ' abcd, ABCE and ABCF

Pattern matching using the ESCAPE clause

You can search for a string that contains one or more special wildcard characters. For example, a discounts table in the customers database might store a percent sign (%) The discount value. To search for a percent semicolon as a character instead of a wildcard, you must provide an escape keyword and an escape character. For example, a sample database contains a column named comment containing text 30%. To search for any row that contains string 30% anywhere in the comment column, specify a WHERE clause consisting of the where comment like '%30!%% '! '. If you do not specify escape and escape characters, SQL Server returns all rows containing the string 30.

The following example shows how to search for a string in the notes column of the Pubs database titles table "50% or more copies are purchased":

SELECT Notes from titles WHERE notes like "50%% off" or more copies are purchased ' ESCAPE '%