LIKE Conditions

The syntax for LIKE conditions is:

expression [ NOT ] LIKE pattern [ ESCAPE escape-expr ]

The LIKE condition can evaluate as TRUE, FALSE, or UNKNOWN. You can use LIKE only on string data.

You cannot use subqueries inside a LIKE predicate.

LIKE predicates that start with characters other than wildcard characters may execute faster if an HG or LF index is available.

Certain LIKE predicates execute faster, if a WD index is available.

Without the NOT keyword, the condition evaluates as TRUE if expression matches the pattern. If either expression or pattern is the NULL value, this condition is UNKNOWN. The NOT keyword reverses the meaning of the condition but leaves UNKNOWN unchanged.

The pattern might contain any number of wildcard characters. The wildcard characters are:

All other characters must match exactly.

For example, the search condition:

name LIKE 'a%b_'

is TRUE for any row where name starts with the letter a and has the letter b as its second-to-last character.

If you specify an escape-expr, it must evaluate to a single character. The character can precede a percent, an underscore, a left square bracket, or another escape character in the pattern to prevent the special character from having its special meaning. When escaped in this manner, a percent matches a percent, and an underscore matches an underscore.

Patterns Supported

All patterns of 126 characters or less are supported.

Some patterns between 127 and 254 characters are supported, but only under certain circumstances. See the following subsections for examples.

All patterns 255 characters or greater are not supported.

Patterns Between 127 and 254 Characters: Example 1

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot compile Like pattern: either bad pattern or pattern too long.

Sybase IQ collapses adjacent constant characters into a single character. For example, consider the following LIKE predicate with a string length of 130 characters:

select col2 from tablen where col2 like '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456%%%%' ;

Sybase IQ collapses the four adjacent constant characters %%%% at the end of the string into one % character, thereby reducing the length of the string from 130 characters to 127. This is less than the maximum of 256 bytes (or 255/2 characters), and no error is generated.

Therefore, if your LIKE predicate contains adjacent constants in the string, patterns of length between 127 and 254 characters are supported as long as the total length of the collapsed string is less than 256 bytes (or 255/2 characters).

Patterns between 127 and 254 characters: example 2

select col2 from tablen where col2 like '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' ;

In this case, no characters are collapsed. The character string length remains at 130 characters and Sybase IQ generates an error.

Patterns Between 127 and 254 Characters: Example 3

select col2 from tablen where col2 like '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456____' ;

Sybase IQ does not collapse adjacent special characters. The string length remains at 130 characters and Sybase IQ generates an error.

Patterns Between 127 and 254 Characters: Example 4

select col2 from tablen where col2 like '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456[1-3]' ;

The length of the LIKE predicate in bytes is calculated as follows: 126 (for the constant characters) * 2 + 1 (for the 1 in brackets) + 1 ( for the 3 in brackets) + 2 ( for the Set state and Range state expression).

This equals 256 bytes, and therefore Sybase IQ generates an error.

Searching for One of a Set of Characters

You can specify a set of characters to look for by listing the characters inside square brackets. For example, the following condition finds the strings smith and smyth:

LIKE 'sm[iy]th'

Searching for One of a Range of Characters

Specify a range of characters to look for by listing the ends of the range inside square brackets, separated by a hyphen. For example, the following condition finds the strings bough and rough, but not tough:

LIKE '[a-r]ough'

The range of characters [a-z] is interpreted as “greater than or equal to a, and less than or equal to z,” where the greater than and less than operations are carried out within the collation of the database. For information on ordering of characters within a collation, see System Administration Guide: Volume 1 > International Languages and Character Sets.

The lower end of the range must precede the higher end of the range. For example, a LIKE condition containing the expression [z-a] returns no rows, because no character matches the [z-a] range.

Unless the database is created as case-sensitive, the range of characters is case-insensitive. For example, the following condition finds the strings Bough, rough, and TOUGH:

LIKE '[a-z]ough'

If the database is created as a case-sensitive database, the search condition is case-sensitive also.

Combining Searches for Ranges and Sets

You can combine ranges and sets within square brackets. For example, the following condition finds the strings bough, rough, and tough:

LIKE '[a-rt]ough'

The bracket [a-mpqs-z] is interpreted as “exactly one character that is either in the range a to m inclusive, or is p, or is q, or is in the range s to z inclusive.”

Searching for One Character Not in a Range

Use the caret character (^) to specify a range of characters that is excluded from a search. For example, the following condition finds the string tough, but not the strings rough, or bough:

LIKE '[^a-r]ough'

The caret negates the entire contents of the brackets. For example, the bracket [^a-mpqs-z] is interpreted as “exactly one character that is not in the range a to m inclusive, is not p, is not q, and is not in the range s to z inclusive.”

Special Cases of Ranges and Sets

Any single character in square brackets indicates that character. For example, [a] matches just the character a. [^] matches just the caret character, [%] matches only the percent character (the percent character does not act as a wildcard character in this context), and [_] matches just the underscore character. Also, [[] matches only the character [.

Other special cases are:

Compatibility

The ESCAPE clause is supported by Sybase IQ only.