Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.0

# Operators Used in the WHERE Clause

A predicate can use the operators as described in Operators. For detailed information about using the LIKE operator, see LIKE Operator.

The following sections contain additional information about using the LIKE operator in the predicate of a WHERE clause.

## Indexed Columns with LIKE Operator

If the left operand of the LIKE operator is the name of an indexed column of type string, ActiveSpaces converts the LIKE operator into a range query using >= and <=. This enables the ActiveSpaces index selection algorithm to select the index and use it for scanning rows when processing the query. For example, look at the following statement:

    SELECT * FROM mytable WHERE lastname LIKE 'Long'

Internally this statement is converted to the following statement and the index on `lastname` can be used to perform a range scan.

    SELECT * FROM mytable WHERE lastname >= 'LONG' AND lastname <= 'long'

However, the index for `lastname` cannot be used if `<character sequence pattern>` is a `long` value or starts with a wildcard (%, _) or digit.

## Pattern Matching with LIKE Operator

When using the LIKE operator in a WHERE clause, using a character sequence pattern that begins with a wild-card results in a full table scan.

It is also important to be aware that you might end up scanning more rows than you would expect because of the way pattern matching works when matching is not case-sensitive. For example, if you have an index defined for `lastname` and you run the following query:

    SELECT * FROM mytable WHERE lastname LIKE
    'm%'

The index is used, but all the rows are scanned where the value of `lastname` start with "M" through "Z" and "a" through "m".

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

Copyright © 2009-2025. Cloud Software Group, Inc. All Rights Reserved.