16.2.2: Wildcard in LIKE clause
- Page ID
- 92216
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]) |
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%’