Background documentationLIKE Condition Locate this document in the navigation structure

 

A LIKE condition is a search condition with a LIKE predicate. The SQL Optimizer can only evaluate an LIKE condition if it meets the following conditions:

  • The LIKE operator was used as the operator.

  • The LIKE condition has the following form: <column_spec> LIKE <extended_value_spec>

  • The value specification used (extended_value_spec) cannot not begin 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 <column_spec> LIKE <extended_value_spec>.

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

Example Example

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

End of the example.

The database system can use the index FULL_NAME_INDEX (name,firstname). The start key is set in the first record, which fills the LIKE condition in the first two character values (Wade,Francisco). The database system sets the stop key when the first record occurs which no longer meets the condition (Weaver,Alfredo).

Result of the EXPLAIN statement

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

You can find additional simple example SQL statements in the SQL Tutorial.

More Information