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,
date/time

The syntax for like is:

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

These are the 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. `rangespec2` indicates the end of a range of characters. The symbol – is a special character, indicating a range. `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 noninclusion. [^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. 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%"

SAP ASE 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, SAP ASE 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.

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 “9:20” into a datetime column named arrival_time, the query below does not find the value, because SAP ASE converts the entry into “Jan 1 1900 9:20AM”:

where arrival_time = "9:20"

However, this query finds the 9:20 value:

where arrival_time like "%9:20%"

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

not like Usage
You can use the same wildcard characters with not like that you can use with like.
Different Results Using not like and ^
You cannot always duplicate not like patterns with like and the negative wildcard character [^]. Match strings with negative wildcard characters are evaluated in steps, one character at a time. If the match fails at any point in the evaluation, it is eliminated.
Use Wildcard Characters as Literal Characters
You can search for wildcard characters by using the escape character and searching for them as literals. There are two ways to use the wildcard characters as literals in a like match string: square brackets and the escape clause.
Interaction of Wildcard Characters and Square Brackets
An escape character retains its special meaning within square brackets, unlike wildcard characters.
Use Trailing Blanks and %
SAP ASE truncates trailing blanks following “%” in a like clause to a single trailing blank. like ‘‘% ’’ (percent sign followed by 2 spaces) matches ‘‘X ’’ (one space); ‘‘X ’’ (two spaces); ‘‘X ’’ (three spaces), or any number of trailing spaces.
Use Wildcard Characters in Columns
You can use wildcard characters for columns and column names. You might want to create a table called special_discounts in the pubs2 database to run a price projection for a special sale:

Parent topic: Select Rows Using the where Clause