BETWEEN

Data Types and SubtypesData Definition (DDL) Statements

`BETWEEN`

Syntax

<value> [NOT] BETWEEN <value_1> AND <value_2>

The BETWEEN predicate tests whether a value falls within a specified range of two values.(NOT BETWEEN tests whether the value does not fall within that range.)

The operands for BETWEEN predicate are two arguments of compatible data types.The BETWEEN predicate in Firebird is asymmetrical — if the lower bound is not the first argument, the BETWEEN predicate will return FALSE.The search is inclusive (the values represented by both arguments are included in the search).In other words, the BETWEEN predicate could be rewritten:

<value> >= <value_1> AND <value> <= <value_2>

When BETWEEN is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it is available.

Example

SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN date '1992-01-01' AND CURRENT_DATE

`LIKE`

Syntax

<match_value> [NOT] LIKE <pattern>
   [ESCAPE <escape character>]

<match_value>      ::= character-type expression
<pattern>          ::= search pattern
<escape character> ::= escape character

The LIKE predicate compares the character-type expression with the pattern defined in the second expression.Case- or accent-sensitivity for the comparison is determined by the collation that is in use.A collation can be specified for either operand, if required.