LIKE
Purpose
Searches for a character string pattern within another character string or character string expression.
Syntax
where:
Syntax Element … |
Specifies … |
expression |
a character string or character string expression argument to be searched for the substring pattern_expression. |
pattern_expression |
a character expression for which expression is to be searched. |
ANY |
a quantifier that allows one or more expressions to be searched for one or more patterns or for one or more values returned by a subquery. SOME is a synonym for ANY. |
subquery |
a SELECT statement argument. A subquery cannot specify a SELECT AND CONSUME statement. |
ESCAPE escape_character |
keyword/variable combination specifying a single escape character (single or multibyte). |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Optimized Performance Using a NUSI
If it is cost-effective, the Optimizer may choose to evaluate a LIKE expression by scanning a NUSI with or without accessing the base table. The cost of using a NUSI depends on the selectivity of the LIKE expression, the size of the NUSI subtable, and if the NUSI is a covering index or a partially covering index. For a partially covering index, the cost of sorting the RowID spool is also included. For details on NUSIs and query covering, see Database Design.
The Optimizer can perform a better cost comparison between using a NUSI and using an all-rows scan if the following are true:
You cannot use a NUSI with a VARCHAR field for processing a LIKE expression when:
d1||‘ab’ LIKE ‘b ab’
In addition, a NUSI with a VARCHAR field cannot be used as a partially covering index for an unconstrained aggregate query.
Null Expressions
If any expression in a comparison is null, the result of the comparison is unknown.
For a LIKE operation to provide a true result when searching fields that may contain nulls, the statement must include the IS [NOT] NULL operator.
Case Specification
If neither pattern_expression nor expression has been designated CASESPECIFIC, any lowercase letters in pattern_expression and expression are converted to uppercase before the comparison operation occurs. If ESCAPE is specified and the escape character is a lowercase character, it is also converted to uppercase before the comparison operation occurs.
If either expression or pattern_expression has been designated CASESPECIFIC, two letters match only if they are the same letters and the same case.
Wildcard Characters
The % and _ characters may be used in any combination in pattern_expression.
Character |
Description |
% (PERCENT SIGN) |
Represents any string of zero or more arbitrary characters. Any string of characters is acceptable as a replacement for the percent. |
_ (LOW LINE) |
Represents exactly one arbitrary character. Any single character is acceptable in the position in which the underscore character appears. |
The underscore and percent characters cannot be used in a pattern. To get around this, specify a single escape character in addition to pattern_expression. For details, see “ESCAPE Feature of LIKE” on page 955.
The following table describes how the metacharacters % and _ (and their fullwidth equivalents) behave when matching strings for various server character sets. Note that ANSI only defines the single byte spacing underscore and percent sign metacharacters.
Teradata SQL extends the permissible metacharacter set for the LIKE predicate to include the fullwidth underscore and the fullwidth percent sign.
FOR this server character set … |
USE this metacharacter … |
TO match this character or characters … |
|
ANSI Mode |
Teradata Mode |
||
KANJI1 |
spacing underscore |
any one single- or multibyte character. |
any one single byte character. |
fullwidth spacing underscore |
any one single byte character or multibyte character. |
any one single byte character or multibyte character. |
|
percent sign |
any sequence of single or multibyte characters. |
any sequence of single byte characters or multibyte characters. |
|
fullwidth percent sign |
any sequence of single or multibyte characters. |
any sequence of single byte characters or multibyte characters. |
|
UNICODE |
fullwidth spacing underscore |
none. These characters are not treated as metacharacters in order to maintain compliance with the ANSI SQL standard. |
|
fullwidth percent |
|||
GRAPHIC |
fullwidth spacing underscore |
any one single GRAPHIC character. |
|
fullwidth percent sign |
any sequence of GRAPHIC characters. |
ESCAPE Feature of LIKE
When the defined ESCAPE character is in the pattern string, it must be immediately followed by an underscore, percent sign, or another ESCAPE character.
In a left-to-right scan of the pattern string the following rules apply when ESCAPE is specified:
Example
The following example illustrates the use of ESCAPE:
To look for the pattern ‘95%’ in a string such as ‘Result is 95% effective’, if Result is the field to be checked, use:
WHERE Result LIKE '%95Z%%' ESCAPE 'Z'
This clause finds the value ‘95%’.
Pad Characters
The following notes apply to pad characters and how they are treated in strings:
For example, ‘A%BC’ matches ‘AxxBC’, but not ‘AxxBCΔ’, and ‘A%BCΔ’ matches ‘AxxBCΔ’, but not ‘AxxBC’ or ‘AxxBCΔΔ’ (Δ indicates a pad character).
For example, to remove trailing pad characters:
TRIM (TRAILING FROM expression) LIKE pattern‑string
To remove leading and trailing pad characters:
TRIM (BOTH FROM expression) LIKE pattern-string
For example, the following statement appends trailing pad characters to pattern strings shorter than 5 characters long.
CREATE MACRO (pattern (CHAR(5)) AS
field LIKE :pattern…
These two methods do not always return the same results.TRIM removes pad characters, while the VARCHAR method maintains the data pattern exactly as entered.
Example
The following example uses the LIKE predicate to select a list of employees whose job title contains the string “Pres”:
SELECT Name, DeptNo, JobTitle
FROM Employee
WHERE JobTitle LIKE '%Pres%' ;
The form %string% requires Teradata Database to examine much of each string x. If x is long and there are many rows in the table, the search for qualifying rows may take a long time.
The result returned is:
Name |
DeptNo |
JobTitle |
Watson L |
500 |
Vice President |
Phan A |
300 |
Vice President |
Russel S |
300 |
President |
Example
This example selects a list of all employees whose last name begins with the letter P.
SELECT Name
FROM Employee
WHERE Name LIKE 'P%';
The result returned is:
Name
----------
Phan A
Peterson J
Example
This example uses the % and _ characters to select a list of employees with the letter A as the second letter in the last name. The length of the return string may be two or more characters.
SELECT Name
FROM Employee
WHERE Name LIKE '_a%';
returns the result:
Name
----------
Marston A
Watson L
Carter J
Replacing _a% with _a_ changes the search to a three‑character string with the letter a as the second character. Because none of the names in the Employee table fit this description, the query returns no rows.
Both leading and trailing pad characters in a pattern are significant to the matching rules.
Example
LIKE ’ΔΔZ%’ locates only those fields that start with two pad characters followed by Z.
ANY/ALL/SOME Quantifiers
SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more values such as shown by the following generic example.
IF you specify this quantifier … |
THEN the search condition is satisfied if expression LIKE pattern_string … is true for … |
ALL |
every string in the list. |
ANY |
any string in the list. |
The ALL quantifier is the logical statement FOR ∀.
The ANY quantifier is the logical statement FOR ∃.
The following table restates this.
THIS expression … |
IS equivalent to this expression … |
x LIKE ALL ('A%','%B','%C%')
|
x LIKE 'A%'
AND x LIKE '%B'
AND x LIKE '%C%'
|
x LIKE ANY ('A%','%B','%C%')
|
x LIKE 'A%'
OR x LIKE '%B'
OR x LIKE '%C%'
|
The following statement selects from the employee table the row of any employee whose job title includes the characters “Pres” or begins with the characters “Man”:
SELECT *
FROM Employee
WHERE JobTitle LIKE ANY ('%Pres%', 'Man%');
The result of this statement is:
EmpNo |
Name |
DeptNo |
JobTitle |
Salary |
10021 |
Smith T |
700 |
Manager |
45, 000.00 |
10008 |
Phan A |
300 |
Vice Pres |
55, 000.00 |
10007 |
Aguilar J |
600 |
Manager |
45, 000.00 |
10018 |
Russell S |
300 |
President |
65, 000.00 |
10012 |
Watson L |
500 |
Vice Pres |
56, 000.00 |
For the following forms, if you specify the ALL or ANY/SOME quantifier, then the subquery may return none, one, or several rows.
If, however, a quantifier is not used, then the subquery must return either no value or a single value as described in the following table.
This expression … |
Is TRUE when expression matches … |
expression LIKE (subquery) |
the single value returned by subquery. |
expression LIKE ANY (subquery) |
at least one value of the set of values returned by subquery; is false if subquery returns no values. |
expression LIKE ALL (subquery) |
each individual value in the set of values returned by subquery, and is true if subquery returns no values. |
Example
The following statement uses the ANY quantifier to retrieve every row from the Project table, which contains either the Accounts Payable or the Accounts Receivable project code:
SELECT * FROM Project
WHERE Proj_Id LIKE ANY
(SELECT Proj_Id
FROM Charges
WHERE Proj_Id LIKE ANY ('A%')) ;
subquery
If the following form is used, the subquery might return none, one, or several values.
The following example shows how you can match using patterns selected from another table.
There are two base tables.
This table … |
Defines these things … |
Project |
|
Department_Proj |
The association between project ID patterns and departments. |
Department_Proj has two columns: Proj_pattern and Department. The rows in this table look like the following.
Proj_pattern |
Department |
AP% |
Finance |
AR% |
Finance |
Nut% |
R&D |
Screw% |
R&D |
The following query uses LIKE to match patterns selected from the Department_Proj table to select all rows in the Project table that have a Proj_Id that matches project patterns associated with the Finance department as defined in the Department_Proj table.
SELECT *
FROM Project
WHERE Proj_Id LIKE ANY
(SELECT Proj_Pattern
FROM Department_Proj
WHERE Department = 'Finance');
When this syntax is used, the subquery must select the same number of expressions as are in the expression list.
For example:
(x,y) LIKE ALL (SELECT a,b FROM c)
is equivalent to:
(x LIKE c.a) AND (y LIKE c.b)
Behavior of the ESCAPE Character
When escape_character is used in (generic) string_2, it must be followed immediately by a metacharacter of the appropriate server character set or another escape_character.
The resultant two-character sequence matches a single character in string_1 if and only if the character in string_1 collates identically to the character following the escape_character in string_2.
In other words, escape_character is ignored for matching purposes and the character following escape_character is matched for a single occurrence of itself.
When string_1 and string_2 do not share a common server character set, then the valid metacharacters are SPACING UNDERSCORE and PERCENT SIGN because the arguments are translated to UNICODE automatically when mismatched. Their behavior then follows the rules described in “Implicit Character-to-Character Translation” on page 603.
Miscellaneous Examples
Function |
Result |
_KanjiSJIS ‘92 |
TRUE |
_KanjiSJIS ‘92 |
FALSE1 |
‘c%’ LIKE ‘c%%’ ESCAPE ‘%’ |
TRUE |
‘c%’ LIKE ‘c%%’ ESCAPE ‘%’ |
FALSE2 |
KanjiEBCDIC Examples
The following examples indicate the behavior of LIKE with KanjiEBCDIC strings using the function (expression LIKE pattern_expression).
expression |
pattern_expression |
Server Character Set |
Result |
MN<AB> |
%<B> |
KANJI1 |
TRUE |
MN<AB>P |
<%B>% |
KANJI1 |
TRUE |
MN<AB>P |
%P |
KANJI1 |
TRUE |
MN<AB>P |
%<__C>% |
KANJI1 |
FALSE |
__ represents a FULLWIDTH UNDERSCORE. |
KanjiEUC Examples
The following examples indicate the behavior of LIKE with KanjiEUC strings using the function (expression LIKE pattern_expression).
expression |
pattern_expression |
Server Character Set |
Result |
ss3A ss2B ss3C ss2D |
% ss2B% |
UNICODE |
TRUE |
M ss2B N ss2D |
M __% |
GRAPHIC |
TRUE |
ss3A ss2B ss3C ss2D |
__% |
KANJISJIS |
TRUE |
ss3A ss2B ss3C ss2D |
_ % |
KANJISJIS |
TRUE |
__ represents a FULLWIDTH UNDERSCORE. |
|||
_ represents a SPACING UNDERSCORE. |
KanjiShift-JIS Examples
The following examples indicate the behavior of LIKE with KanjiShift-JIS strings using the function (expression LIKE pattern_expression).
expression |
pattern_expression |
Server Character Set |
ANSI Mode Result |
Teradata Mode Result |
ABCD |
__B% |
GRAPHIC |
TRUE |
TRUE |
mnABCI |
%B% |
UNICODE |
TRUE |
TRUE |
mnABCI |
%I |
UNICODE |
TRUE |
TRUE |
mnABCI |
mn_%I |
KANJI1 |
TRUE The underscore in pattern_expression matches a single byte- or multibyte character in ANSI mode. |
FALSE The underscore in pattern_expression matches a single byte character in Teradata mode. |
mnABCI |
mn__%I |
KANJI1 |
TRUE |
TRUE |
__ represents a FULLWIDTH UNDERSCORE. |
||||
_ represents a SPACING UNDERSCORE. |