LIKE Transformations

• Character String Beginning with Constant
• Character String Without Wildcards
• Unknown Parameter

A LIKE predicate in which a column is compared to a character string that begins with a character constant (not a wildcard) is transformed into three predicates: one predicate that uses the LIKE operator, one that uses the >= operator, and one that uses the < operator. For example:

country LIKE 'Ch%i%'

becomes

country LIKE 'Ch%i%'
AND country >= 'Ch'
AND country < 'Ci'

The first (LIKE) predicate is not optimizable, but the new predicates added by the transformation are.

When the character string begins with one more character constants and ends with a single "%", the first LIKE clause is eliminated. For example:

country LIKE 'Ch%'

becomes

country >= 'Ch'
AND country < 'Ci'

A LIKE predicate is transformed into a predicate that uses the = operator (and a NOT LIKE predicate is transformed into one that uses <>) when the character string does not contain any wildcards. For example:

country LIKE 'Chile'

becomes

country = 'Chile'

and

country NOT LIKE 'Chile'

becomes

country <> 'Chile'

Predicates that use the = operator are optimizable. Predicates that use the <> operator are sargable.

'The situation is similar to those described above when a column is compared using the LIKE operator to a parameter whose value is unknown in advance (dynamic parameter, join column, etc.).

In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate, one predicate using the >= operator, and one predicate using the < operator. For example:

country LIKE ?

is transformed into

country LIKE ?
AND country >= InternallyGeneratedParameter
AND country < InternallyGeneratedParameter

where the InternallyGeneratedParameters are calculated at the beginning of execution based on the value of the parameter.

Note:

This transformation can lead to a bad plan if the user passes in a string that begins with a wildcard or a nonselective string as the parameter. Users can work around this possibility by writing the query like this (which is not optimizable):

(country || '') LIKE ?