Show TOC

LIKE ConditionLocate this document in the navigation structure

Use

A LIKE condition is a search condition with a LIKE predicate.

Prerequisites

The SQL optimizer can only evaluate a LIKE condition if it meets the following conditions:

  • Only the LIKE operator was used as operator.

  • The LIKE condition has the following form:

    						
<column_spec> LIKE <extended_value_spec>

    <extended_value_spec> must not start with the match_string or match_set SQL syntax elements of a LIKE predicate.

    If possible, the database system converts conditions of the form

    						
... NOT (<column_spec> NOT LIKE <extended_value_spec>)

    into an expression of the form

    						
... (<column_spec> LIKE <extended_value_spec>
    Note

    The SQL optimizer can only transfer queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).

    For more information, see Database Administration, Special Database Parameters.

Example

The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL (see Concepts of the Database System, Objects in the Schema HOTEL).

LIKE Condition

EXPLAIN SELECT *

  FROM hotel.customer

    WHERE name LIKE 'Wa%'

  • Qualification: name LIKE 'Wa%'

  • Primary key of CUSTOMER table: cno

  • Indexes via qualified columns: FULL_NAME_INDEX (name,firstname)

  • Search strategy: RANGE CONDITION FOR INDEX

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

FULL_NAME_INDEX

RANGE CONDITION FOR INDEX

34

NAME

(USED INDEX COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

4

The database system can use the index FULL_NAME_INDEX (name, firstname). It sets the start key in the first record that meets the LIKE condition with the first two character values ( Wade, Francisco), and the stop key in the first record that does not meet the condition any more ( Weaver, Alfredo).

More Information