The LIKE predicate compares a column of a base table or view with a pattern.
One of the six predicates which constitute a search term.
|
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. |
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.
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.
"_' takes the place of any single character in the pattern. If a particular position in the string be of no significance, 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.
'%' takes the place of zero or more characters in the pattern.
If the pattern were 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 false:
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 result in a row containing the value 'TIMMY ', for example, being found.
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'.
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.
None.
None.
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';