SELECT Statement

Using wildcards in the LIKE clause

The LIKE keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime, and smalldatetime data. A wildcard allows the user to match fields that contain certain letters. For example, the wildcard province = 'N%' would give all provinces that start with the letter 'N'. Table 16.3 shows four ways to specify wildcards in the SELECT statement in regular express format.

%

 Any string of zero or more characters

_

 Any single character

[ ]

 Any single character within the specified range (e.g., [a-f]) or set (e.g., [abcdef])

[^]

 Any single character not within the specified range (e.g., [^a – f]) or set (e.g., [^abcdef])


Table 16.3. How to specify wildcards in the SELECT statement.

In example #1, LIKE 'Mc%' searches for all last names that begin with the letters "Mc" (e.g., McBadden).

SELECT LastName
FROM Employees
WHERE LastName LIKE 'Mc%'

For example #2: LIKE '%inger' searches for all last names that end with the letters "inger" (e.g., Ringer, Stringer).

SELECT LastName
FROM Employees
WHERE LastName LIKE '%inger'

In, example #3: LIKE '%en%' searches for all last names that have the letters "en" (e.g., Bennett, Green, McBadden).

SELECT LastName
FROM Employees
WHERE LastName LIKE '%en%'