Chapter 2: Queries: Selecting Data from a Table

Matching character strings: like

The like keyword searches for a character string that matches a pattern. like is used with char, varchar, nchar, nvarchar, unichar, unitext, univarchar binary, varbinary, text, and date/time data.

The syntax for like is:

{where | having} [not] 
     column_name [not] like  "match_string"

match_string can include the symbols in Table 2-4:

**Table 2-4: Special symbols for matching character strings**
Symbols	Meaning
%	Matches any string of zero or more characters.
_	Matches a single character.
[specifier]	Brackets enclose ranges or sets, such as [a – f] or [abcdef]. specifier can take two forms: rangespec1-rangespec2: rangespec1 indicates the start of a range of characters. – is a special character, indicating a range. rangespec2 indicates the end of a range of characters. set: can be composed of any discrete set of values, in any order, such as [a2bR].The range [a – f], and the sets [abcdef] and [fcbdae] return the same set of values. Specifiers are case-sensitive.
[^specifier]	A caret (^) preceding a specifier indicates non-inclusion. [^a – f] means “not in the range a – f”; [^a2bR] means “not a, 2, b, or R.”

You can match the column data to constants, variables, or other columns that contain the wildcard characters shown in Table 2-4. When using constants, enclose the match strings and character strings in quotation marks. For example, using like with the data in the authors table:

like “Mc%” searches for every name that begins with ‘‘Mc’’ (McBadden).
like “%inger” searches for every name that ends with ‘‘inger’’ (Ringer, Stringer).
like “%en%” searches for every name containing ‘‘en’’ (Bennet, Green, McBadden).
like “_heryl” searches for every six-letter name ending with ‘‘heryl’’ (Cheryl).
like “[CK]ars[eo]n” searches for ‘‘Carsen,’’ ‘‘Karsen,’’ ‘‘Carson,’’ and ‘‘Karson’’ (Carson).
like “[M-Z]inger” searches for all names ending with ‘‘inger’’ that begin with any single letter from M to Z (Ringer).
like “M[^c]%” searches for all names beginning with ‘‘M’’ that do not have ‘‘c’’ as the second letter.

This query finds all the phone numbers in the authors table that have an area code of 415:

select phone 
from authors 
where phone like "415%"

The only where condition you can use on text columns is like. This query finds all the rows in the blurbs table where the copy column includes the word “computer”:

select * from blurbs 
where copy like "%computer%"

Adaptive Server interprets wildcard characters used without like as literals rather than as a pattern; they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters “415%” only. It does not find phone numbers that start with 415.

select phone 
from authors 
where phone = "415%"

When you use like with datetime values, Adaptive Server converts the values to the standard datetime format, and then to varchar or univarchar. Since the standard storage format does not include seconds or milliseconds, you cannot search for seconds or milliseconds with like and a pattern.

It is a good idea to use like when you search for date and time values, since these datatype entries may contain a variety of date parts. For example, if you insert the value “9:20” into a datetime column named arrival_time, this query will not find the value, because Adaptive Server converts the entry into “Jan 1 1900 9:20AM”:

where arrival_time = "9:20"

However, the clause below finds the 9:20 value:

where arrival_time like "%9:20%"

You can also use the date and time datatypes for like transactions.

View this book as PDF