LIKE Predicate

Tests for a matching pattern.

Syntax

Arguments

Details

Overview of the LIKE Predicate

Patterns for Searching

Searching for Mixed-Case Strings

Example

Syntax

expression [NOT] LIKE expression

Arguments

expression

specifies any valid SQL expression that is either a character string type or a binary string type.

Tip	The SQL expression on the right side of the syntax, that is the pattern, is most likely to be a literal.
See	<sql-expression>
See	FedSQL Expressions

Details

Overview of the LIKE Predicate

The LIKE predicate selects rows by comparing character strings with a pattern-matching specification. It resolves to true and displays the matched string or strings if the left operand matches the pattern that is specified by the right operand.

Escape characters are not supported.

Note: If no rows are returned, the result is a null value.

Patterns for Searching

Patterns include three classes of characters:

underscore (_)

matches any single character.

percent sign (%)

matches any sequence of zero or more characters.

any other character

matches that character.

These patterns can appear before, after, or on both sides of characters that you want to match. The LIKE condition is case-sensitive.

The following list uses these values: Smith, Smooth, Smothers, Smart, and Smuggle.

'Sm%'

matches Smith, Smooth, Smothers, Smart, Smuggle.

'%th'

matches Smith, Smooth.

'S__gg%'

matches Smuggle.

'S_o'

matches a three-letter word, so it has no matches here.

'S_o%'

matches Smooth, Smothers.

'S%th'

matches Smith, Smooth.

'M'

matches the single, uppercase character m only, so it has no matches here.

Searching for Mixed-Case Strings

To search for mixed-case strings, use the UPPER function to make all the names uppercase before entering the LIKE condition:

   upper(name) like 'SM%';

Note: When you are using the % character, be aware of the effect of trailing blanks. You might have to use the TRIM function to remove trailing blanks in order to match values.

Example

Table: DENSITIES

select name, population
   from densities
      where name like 'Al%';

LIKE Predicate

Syntax

Arguments

expression

Details

Overview of the LIKE Predicate

Patterns for Searching

Searching for Mixed-Case Strings

Example

See Also