LIKE Predicate
Function
The LIKE predicate compares a column of a base table or view with a pattern.
Invocation
One of the six predicates which constitute a search term.
Syntax
expression_1 [NOT] LIKE expression_2
where
column specification | A column of a base table or view which is to provide the value against which the comparison is to be made. The column must be of data type character-string. |
NOT | An operator which negates the result of the LIKE predicate. |
pattern | The form to which the column must conform. It can be expressed as either a hard coded constant or a single host variable specification of the data type character string. The use of wildcard characters is supported. |
escape character | A single escape character. The wildcard characters themselves can be considered in any pattern matching by preceding them with an escape character. |
Description
The LIKE predicate performs a comparison between the specified column value and a given pattern. If a match is found, the predicate equates to true; otherwise it is false. If the column or the pattern equates to a NULL value, the predicate has an unknown result.
Wildcard Characters
For a true predicate, there needs to be a one-to-one match between the two strings. You can use wildcard characters to make the comparison more flexible.
You can specify wildcard characters anywhere in the pattern.
Wildcard Character "_""_" takes the place of any single character in the pattern. If a particular position in the string is insignificant, use an underscore character in the pattern to mask it out.
For example, with a pattern of `ABCDE', only `ABCDE' will result in `true'. However, a pattern of `AB_DE' will not only give a true result for `ABCDE' as before but also for `ABZDE' or, in fact, for any string that is five characters long and starts with `AB' and ends with `DE'. Note the comparison of `ABZZDE' would fail for this pattern as an extra character has been introduced.
Wildcard Character "%" "%" takes the place of zero or more characters in the pattern.
If the pattern is specified as `AB%DE', then a column value of `ABZZDE' would give a true result as would a string of any length that started with `AB' and finished with `DE'.
If the pattern is not of an identical size to the column, no space padding takes place and so, no match will be found. This is opposite to a normal COMPARISON predicate.
For example, if the column first_name has provision for 10 characters and contains the value 'TIMOTHY' then the following COMPARISON predicate will evaluate to true:
WHERE first_name = 'TIMOTHY'
However, the following LIKE predicate will evaluate to fasle:
first_name LIKE 'TIMOTHY'
This is because no space padding takes place. The following two LIKE predicates would evaluate to true:
first_name LIKE 'TIMOTHY '
first_name LIKE 'TIM%'
Note:
In the above case, the wildcard character % would also find a row containing the value 'TIMMY ' true.
The Escape Character "^" If either or both of the wildcard characters are required to have their actual meaning, then specify an escape character. An escape character is any single character which must precede either the "%"or the "_" thus signifying that the following wildcard character is to be taken literally.
For example, if an exact match for the string 'AB_DE' was required and the escape character had been defined as "^", then the pattern would have to be specified as 'AB^_DE'.
Limitations
Should the column reference a view, then this viewed column must be derived exclusively from a column of a base table. This applies to all three modes.
Example
The following example selects a person whose name ends with the characters 'ann' :
SELECT person_id
FROM person
WHERE first_name_1 LIKE '%ann';