3 EPL Reference: Operators

3.7.1 IN Operator

The IN operator determines if a given value matches any value in a list. The syntax of the operator is:

test_expression [NOT] IN (expression [,expression [,…]] )

The test_expression is any valid expression. The IN keyword is followed by a list of expressions to test for a match. The optional NOT keyword specifies that the result of the predicate be negated.

The result of an IN expression is of type Boolean. If the value of test_expression is equal to any expression from the comma-separated list, the result value is true. Otherwise, the result value is false. All expressions must be of the same type or a type compatible with test_expression.

The next example shows how the IN keyword can be applied to select certain command types of RFIDEvents:

SELECT * 
  FROM RFIDEvent RETAIN ALL 
  WHERE command IN ('OBSERVATION', 'SIGNAL')

The statement is equivalent to:

SELECT * 
  FROM RFIDEvent RETAIN ALL 
  WHERE command = 'OBSERVATION' OR symbol = 'SIGNAL'

3.7.2 BETWEEN Operator

The BETWEEN operator specifies a range to test. The syntax of the operator is:

test_expression [NOT] BETWEEN begin_expression AND end_expression

The test_expression is any valid expression and is the expression to test for the range being inclusively within the expressions defined by begin_expression and end_expression. The NOT keyword specifies that the result of the predicate be negated.

The result of a BETWEEN expression is of type Boolean. If the value of test_expression is greater then or equal to the value of begin_expression and less than or equal to the value of end_expression, the result is true.

The next example shows how the BETWEEN keyword can be used to select events with a price between 55 and 60 (inclusive).

SELECT * 
  FROM StockTickEvent RETAIN ALL 
  WHERE price BETWEEN 55 AND 60

The equivalent expression without using the BETWEEN keyword is:

SELECT * 
  FROM StockTickEvent RETAIN ALL 
  WHERE price >= 55 AND price <= 60

The begin_expression and end_expression may occur in either order without affecting the query. For example, the following is equivalent to the above example:

SELECT * 
  FROM StockTickEvent RETAIN ALL 
  WHERE price BETWEEN 60 AND 55

3.8.1 LIKE Operator

The LIKE operator provides standard SQL pattern matching. SQL pattern matching allows you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In EPL, SQL patterns are case-sensitive by default. The syntax of LIKE is:

test_expression [NOT] LIKE pattern_expression [ESCAPE string_literal]

The test_expression is any valid expression yielding a String type or a numeric result. The optional NOT keyword specifies that the result of the predicate be negated. The LIKE keyword is followed by any valid standard SQL pattern_expression yielding a String-typed result. The optional ESCAPE keyword signals the escape character used to escape the _ and % values in the pattern.

The result of a LIKE expression is of type Boolean. If the value of test_expression matches the pattern_expression, the result value is true. Otherwise, the result value is false. An example for the LIKE keyword is shown below.

SELECT * 
  FROM PersonLocationEvent RETAIN ALL 
  WHERE name LIKE '%Jack%'

In this example the WHERE clause matches events where the suffix property is a single _ character.

SELECT * 
  FROM PersonLocationEvent RETAIN ALL 
  WHERE suffix LIKE '!_' ESCAPE '!'

3.8.2 REGEXP Operator

The REGEXP operator is a form of pattern matching based on regular expressions implemented through the Java java.util.regex package. The syntax of REGEXP is:

test_expression [NOT] REGEXP pattern_expression

The test_expression is any valid expression yielding a String type or a numeric result. The optional NOT keyword specifies that the result of the predicate be negated. The REGEXP keyword is followed by any valid regular expression pattern_expression yielding a String-typed result.

The result of a REGEXP expression is of type Boolean. If the value of test_expression matches the regular expression pattern_expression, the result value is true. Otherwise, the result value is false.

An example for the REGEXP operator is below.

SELECT * 
  FROM PersonLocationEvent RETAIN ALL 
  WHERE name REGEXP '*Jack*'

3.9.1 FOLLOWED BY Operator

The FOLLOWED BY operator specifies that first the left hand expression must turn true and only then is the right hand expression evaluated for matching events.

For example, the following pattern looks for event A and if encountered, looks for event B:

A FOLLOWED BY B

This does not mean that event A must immediately be followed by event B. Other events may occur between the event A and the event B and this expression would still evaluate to true. If this is not the desired behavior, the NOT operator can be used.

3.9.2 WITHIN Operator

The WITHIN qualifier acts like a stopwatch. If the associated pattern expression does not become true within the specified time period it is evaluated by the engine as false. The WITHIN qualifier takes a time period as a parameter as specified in Section 2.4.4, "Specifying Time Interval."

This pattern fires if an A event arrives within 5 seconds after statement creation.

A WITHIN 5 seconds

This pattern fires for all A events that arrive within 5 second intervals.

3.9.3 EVERY Operator

The EVERY operator indicates that the pattern sub-expression should restart when the sub-expression qualified by the EVERY keyword evaluates to true or false. In the absence of the EVERY operator, an implicit EVERY operator is inserted as a qualifier to the first event stream source found in the pattern not occurring within a NOT expression.

The EVERY operator works like a factory for the pattern sub-expression contained within. When the pattern sub-expression within it fires and thus quits checking for events, the EVERY causes the start of a new pattern sub-expression listening for more occurrences of the same event or set of events.

Every time a pattern sub-expression within an EVERY operator turns true the engine starts a new active sub-expression looking for more event(s) or timing conditions that match the pattern sub-expression.

This pattern fires when an A event is followed by a B event and continues attempting to match again after the B event:

EVERY (A FOLLOWED BY B)

This pattern also fires when an A event is followed by a B event, but continues attempting to match again after the A event:

EVERY A FOLLOWED BY B

The EVERY in this pattern is optional, since it would implicitly be placed here if it was absent.