15.00 - LIKE - Teradata Database

LIKE

Purpose  

Searches for a character string pattern within another character string or character string expression.

Syntax  

where:

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.

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.

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:

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.

The ALL quantifier is the logical statement FOR ∀.

The ANY quantifier is the logical statement FOR ∃.

The following table restates this.

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:

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.

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.

Department_Proj has two columns: Proj_pattern and Department. The rows in this table look like the following.

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

KanjiEBCDIC Examples

The following examples indicate the behavior of LIKE with KanjiEBCDIC strings using the function (expression LIKE pattern_expression).

KanjiEUC Examples

The following examples indicate the behavior of LIKE with KanjiEUC strings using the function (expression LIKE pattern_expression).

KanjiShift-JIS Examples

The following examples indicate the behavior of LIKE with KanjiShift-JIS strings using the function (expression LIKE pattern_expression).