LIKE Condition 

LIKE conditions are search conditions with a LIKE predicate.

For the SQL Optimizer, the following restrictions apply to LIKE conditions:

●     The operator is the operator LIKE.

●     LIKE conditions have the following format:
<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.

The LIKE condition must have one of these forms before the SQL Optimizer can evaluate it to determine a search strategy.

Conditions with a ...NOT format (<column_spec> NOT LIKE <extended_value_spec>) are, where possible, converted into an <column_spec> LIKE <extended_value_spec> expression.

The SQL Optimizer then continues to process the transformed condition.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

SELECT * FROM hotel.customer
  WHERE name LIKE 'Wa%'

Qualification: name LIKE = 'Wa%'
Primary key of the table CUSTOMER: cno
Indexes of qualified columns: FULL_NAME_INDEX (name, firstname)
Search strategy used: RANGE CONDITION FOR INDEX

The index FULL_NAME_INDEX (name, firstname) can be used. The start key is set in the first record, which fills the LIKE condition in the first two character values (Wade, Francisco). The stop key is in the first record that does not fulfill 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 will find additional simple example SQL statements in the SQL Tutorial.

See also:

EXPLAIN Statement

SQL Reference Manual, LIKE Predicate (like_predicate)