Matching Character Strings: like

The like keyword searches for a character string that matches a pattern.

like is used with:

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:
  • rangespec1rangespec2:

    • 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:
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.