15.00 - LIKE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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
ALL
SOME

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:

  • There are statistics collected for both the base table primary index and for the NUSI columns against which the expression string is evaluated.
  • The expression string is either the mode or max value in at least one interval in the base table statistics histogram.
  • You cannot use a NUSI with a VARCHAR field for processing a LIKE expression when:

  • the NUSI contains a VARCHAR field, and the VARCHAR field is used in a NOT LIKE operation.
  • the NUSI contains a VARCHAR field, and the VARCHAR field is used in a string function. For example, the following is not allowed if d1 is a NUSI column of VARCHAR type.
  • 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
    LATIN
    KANJISJIS

    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:

  • Until an instance of the ESCAPE character occurs, characters in the pattern are interpreted at face value.
  • When an ESCAPE character immediately follows another ESCAPE character, the two character sequence is treated as though it were a single instance of the ESCAPE character, considered as a normal character.
  • When an underscore metacharacter immediately follows an ESCAPE character, the sequence is treated as a single underscore character (not a wildcard character).
  • When a percent metacharacter immediately follows an ESCAPE character, the sequence is treated as a single percent character (not a wildcard character).
  • When an ESCAPE character is not immediately followed by an underscore metacharacter, a percent metacharacter, or another instance of itself, the scan stops and an error is reported.
  • 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:

  • Pad characters are significant in both the character expression, and in the pattern string.
  • When using pattern matching, be aware that both leading and trailing pad characters in the field or expression must match exactly with the pattern.
  • For example, ‘A%BC’ matches ‘AxxBC’, but not ‘AxxBCΔ’, and ‘A%BCΔ’ matches ‘AxxBCΔ’, but not ‘AxxBC’ or ‘AxxBCΔΔ’ (Δ indicates a pad character).

  • To retrieve the row in all cases, consider using the TRIM function, which removes both leading and trailing pad characters from the source string before doing the pattern match.
  • 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
  • If pattern_expression is forced to a fixed length, trailing pad characters might be appended. In such cases, the field must contain the same number of trailing pad characters in order to match.
  • 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…
  • To retrieve the row in all cases, apply the TRIM function to the pattern string (TRIM (TRAILING FROM :pattern) ), or the macro parameter can be defined as VARCHAR.
  • 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

  • Unique project ID
  • Project description
  • 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 ‘92abc’ LIKE _Unicode ‘%abc’

    TRUE

    _KanjiSJIS ‘92abc’ LIKE _Unicode ‘%abc’

    FALSE1

    ‘c%’ LIKE ‘c%%’ ESCAPE ‘%’

    TRUE

    ‘c%’ LIKE ‘c%%’ ESCAPE ‘%’

    FALSE2


    1
    % (FULLWIDTH PERCENT SIGN) is not a metacharacter in either KanjiSJIS or Unicode.

    2
    % (FULLWIDTH PERCENT SIGN) does not match % (PERCENT SIGN).

    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.