Like Predicate
The like predicate performs pattern matching for the character data types (character and varchar). The like predicate has the following syntax:
expression [NOT] LIKE pattern [ESCAPE escape_character]
The expression can be a column name or an expression involving string functions.
The pattern parameter must be a string literal. The pattern‑ matching (wildcard) characters are the percent sign (%) to denote 0 or more arbitrary characters, and the underscore (_) to denote exactly one arbitrary character.
The like predicate does not handle trailing blanks. If matching a character data type or if the value has user‑inserted trailing blanks, these trailing blanks must be included in your pattern. For example, if you are searching a character(10) column for any rows that have the name harold, use the following syntax for the like predicate:
name like 'harold '
Four blanks are added to the pattern after the name in order to include the trailing blanks.
Because blanks are not significant when performing comparisons of c data types, the like predicate will return a correct result regardless of whether trailing blanks are included in the pattern.
If the escape clause is specified, the escape character suppresses any special meaning for the following character, allowing the character to be entered literally. The following characters can be escaped:
• The pattern matching characters % and _.
• The escape character itself. To enter the escape character literally, type it twice.
• Brackets [ ]. In escaped brackets ( [ and ] ), specify a series of individual characters or a range of characters separated by a dash (‑).
The following examples illustrate some uses of the pattern matching capabilities of the like predicate:
To match any string starting with 'a':
name like 'a%'
To match any string starting with A through Z:
name like '\[A‑Z\]%' escape '\'
To match any two characters followed by '25%':
name like '__25\%' escape '\'
To match a string starting with a backslash:
name like '\%'
Because there is no escape clause, the backslash is taken literally.
To match a string starting with a backslash and ending with a percent:
name like '\\%\%' escape '\'
To match any string starting with 0 through 4, followed by an uppercase letter, then a [, any two characters and a final ]:
name like '\[01234\]\[A‑Z\][__]' escape '\'
To detect names starting with 'S' and ending with 'h', disregarding any leading or trailing spaces:
trim(name) like 'S%h'
To detect a single quote, the quote must be repeated:
name like ''''
Last modified date: 08/28/2024