Comparison operators, such as =, !=, <, >, LIKE, and IN, can be used in condition_expressions of the WHERE clause in the ADQL query statement. 

This page describes the comparison operators that you can use in the condition_expression syntax.

Operators

Comparisons on strings are case-insensitive.

OperatorDescriptionSupported Usage
=Equals: True if the value of the specified field_name equals the specified value in the expression. String comparisons using the equals operator are case-sensitive.non-analyzed fields
!=Not equals: True if the value in the specified field_name does not equal the specified value.non-analyzed fields
>Greater than: True if the value in the specified field_name is greater than the specified value.non-analyzed fields of type int, float and date
>=Greater than or equal: True if the value in the specified field_name is greater than or equal to the specified value.non-analyzed fields of type int, float and date
<Less than: True if the value in the specified field_name is less than the specified value.non-analyzed fields of type int, float and date
<=Less than or equals: True if the value in the specified field_name is less than or equal to the specified value.non-analyzed fields of type int, float and date
IN

Finds records where the value of a field equals any one of the specified values in a list of string or numeric values. The values for IN must be in parentheses. String values must be surrounded by single quotes. Wild card usage within the list of values provided to the IN operator is supported.

For example:

SELECT transactionName, percentile(segments.transactionTime, 90) FROM transactions 
WHERE application = 'prd-analytics' AND transactionName IN ('api_v2./v2/events/*/search','api_v1./v1/events/*/search')

non-analyzed fields only. Invalid for analyzed fields.

LIKE

The LIKE operator performs a full-text search that analyzes the string value and field value. If the string value contains multiple terms, separated by spaces, it is processed as an AND condition of the terms. Use LIKE to search values in analyzed fields.

The LIKE operator supports the use of wild cards.

LIKE is not supported for queries on mixed case strings. For example, querying against a log message that contains "pEer" will not match the search string 'peer', because "pEer" is tokenized by the search analyzer into two separate tokens - "p" and "eer").

for full-text search of analyzed fields
BETWEEN

Finds values of a field based on an inclusive or exclusive value range. For an inclusive range search, use square brackets [ ]. For an exclusive value range, use parentheses ( ).

Supported for data types int, float, and datetime (New in 4.4.1).
IS NULLFinds records where a field does not contain a value.non-analyzed fields and analyzed fields
IS NOT NULL Finds records where a field contains a value.analyzed and non- analyzed fields
NOT BETWEEN Finds records where a field does not contain a value in an inclusive or exclusive value range. The reverse of BETWEEN. non-analyzed fields
NOT LIKEThe reverse of LIKE.analyzed fields
NOT IN

Finds records where the value of a field does not match any value in a list of string or numeric values. The values for NOT IN must be in parentheses, and string values must be surrounded by single quotes.

There is also a logical operator NOT, which is unrelated to this comparison operator.

non-analyzed fields
REGEXPThe REGEXP operator applies a regular expression pattern match of a string to the pattern passed as an argument. See REGEXP Operator for details of usage. This is an advanced feature and requires knowledge of regular expression patterns.

non-analyzed fields

For analyzed fields:

  • Use lowercase input strings.
  • Do not use to search across tokens

*

?

The wild card operators. Use of wild cards is supported on queries of string fields. The asterisk, '*', character matches zero or more characters and the question mark, '?', character matches a single character.

The wild card operator works similarly when used with the equals "=" or LIKE operator.

The wild card operator is case sensitive for non-analyzed fields.

analyzed fields and non-analyzed fields

Examples

QueryResult

SELECT * FROM mobile_snapshots WHERE appkey='your_appkey' AND numeric_field_name IN (1419, 613, 748)

Returns all fields for mobile snapshots for the specified appkey where the value of the specified numeric field matches one of the values in parentheses.

SELECT count(*) FROM browser_records WHERE appkey='your_appkey' AND field_name IN ('string1','string2', 'string3')

Returns a count of browser records for the specified appkey where the value of the specified field matches one of the specified strings.
SELECT ip FROM browser_records WHERE ip LIKE "10.134.*"By filtering on IP address range using wild card operator, finds records in the specified IP address range.
SELECT * FROM logs WHERE message LIKE 'DEBUG Adding operation'Matches the sample log line "DEBUG com.appdynamics.operations.PublishTestService - Adding to bulk publish operation."
SELECT * FROM logs WHERE message LIKE 'error'Finds all the log events containing the word 'Error' in any field

SELECT * FROM transactions WHERE application='yourAppName' AND cartTotal BETWEEN [10, 50]

Inclusive range: Returns all transactions with a cart total between 10 and 50, including 10 and 50.
SELECT * FROM transactions WHERE application='yourAppName' AND cartTotal BETWEEN (10, 50)Exclusive range: Returns all transactions with cart total between 10 and 50, excluding 10 and 50.