For the complete list of comparison operators with their variant forms, see .

    If one of the sides (left or right) of a comparison predicate has NULL in it, the value of the predicate will be UNKNOWN.

    Examples

    1. Retrieve information about computers with the CPU frequency not less than 500 MHz and the price lower than $800:

      1. SELECT *
      2. FROM Pc
      3. WHERE speed >= 500 AND price < 800;

    2. Retrieve information about all dot matrix printers that cost less than $300:

      1. SELECT *
      2. FROM Printer
      3. WHERE ptrtype = 'matrix' AND price < 300;

    3. The following query will return no data, even if there are printers with no type specified for them, because a predicate that compares NULL with NULL returns NULL:

      1. SELECT *
      2. FROM Printer
      3. WHERE ptrtype = NULL AND price < 300;

      On the other hand, ptrtype can be tested for NULL and return a result: it is just that it is not a comparison test:

      1. SELECT *
      2. FROM Printer
      3. WHERE ptrtype IS NULL AND price < 300;

       — see IS [NOT] NULL.

    Other comparison predicates are marked by keyword symbols.

    BETWEEN

    Available

    DSQL, PSQL, ESQL

    Syntax

    1. <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. Unlike in some other DBMS, the BETWEEN predicate in Firebird is not symmetrical — if the lower value is not the first argument, the BETWEEN predicate will always 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:

    1. <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

    1. SELECT *
    2. FROM EMPLOYEE
    3. WHERE HIRE_DATE BETWEEN date '01.01.1992' AND CURRENT_DATE

    LIKE

    Available

    DSQL, PSQL, ESQL

    Syntax

    1. <match value> [NOT] LIKE <pattern>
    2.    [ESCAPE <escape character>]
    4. <match value>      ::= character-type expression
    5. <pattern>          ::= search pattern
    6. <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.

    Wildcards

    Two wildcard symbols are available for use in the search pattern:

    • the percentage symbol (%) will match any sequence of zero or more characters in the tested value

    • the underscore character (_) will match any single character in the tested value

    If the tested value matches the pattern, taking into account wildcard symbols, the predicate is TRUE.

    Using the ESCAPE Character Option

    If the search string contains either of the wildcard symbols, the ESCAPE clause can be used to specify an escape character. The escape character must precede the ‘%’ or ‘_’} symbol in the search string, to indicate that the symbol is to be interpreted as a literal character.

    Examples using LIKE

    1. Find the numbers of departments whose names start with the word “Software”:

      1. SELECT DEPT_NO
      2. FROM DEPT
      3. WHERE DEPT_NAME LIKE 'Software%';

      It is possible to use an index on the DEPT_NAME field if it exists.

    2. Search for employees whose names consist of 5 letters, start with the letters “Sm” and end with “th”. The predicate will be true for such names as “Smith” and “Smyth”.

      1. SELECT
      2.   first_name
      3. FROM
      4.   employee
      5. WHERE first_name LIKE 'Sm_th'

    3. Search for all clients whose address contains the string “Rostov”:

      1. SELECT *
      2. FROM CUSTOMER
      3. WHERE ADDRESS LIKE '%Rostov%'

    4. Search for tables containing the underscore character in their names. The ‘#’ character is used as the escape character:

      1. SELECT
      2.   RDB$RELATION_NAME
      3. FROM RDB$RELATIONS
      4. WHERE RDB$RELATION_NAME LIKE '%#_%' ESCAPE '#'

    See also

    , CONTAINING,

    STARTING WITH

    Available

    DSQL, PSQL, ESQL

    The predicate searches for a string or a string-like type that starts with the characters in its value argument. The search is case-sensitive.

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

    Example

    Search for employees whose last names start with “Jo”:

    1. SELECT LAST_NAME, FIRST_NAME
    2. FROM EMPLOYEE
    3. WHERE LAST_NAME STARTING WITH 'Jo'

    See also

    CONTAINING

    Available

    DSQL, PSQL, ESQL

    Syntax

      The CONTAINING predicate searches for a string or a string-like type looking for the sequence of characters that matches its argument. It can be used for an alphanumeric (string-like) search on numbers and dates. A CONTAINING search is not case-sensitive. However, if an accent-sensitive collation is in use then the search will be accent-sensitive.

      When CONTAINING is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if a suitable one exists.

      Examples

      1. Search for projects whose names contain the substring “Map”:

        1. SELECT *
        2. FROM PROJECT
        3. WHERE PROJ_NAME CONTAINING 'Map';

        Two rows with the names “AutoMap” and “MapBrowser port” are returned.

      2. Search for changes in salaries with the date containing number 84 (in this case, it means changes that took place in 1984):

        1. SELECT *
        2. FROM SALARY_HISTORY
        3. WHERE CHANGE_DATE CONTAINING 84;

      See also

      SIMILAR TO

      Available

      DSQL, PSQL

      Syntax

      1. string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]
      3. <pattern>     ::= an SQL regular expression
      4. <escape-char> ::= a single character

      SIMILAR TO matches a string against an SQL regular expression pattern. Unlike in some other languages, the pattern must match the entire string in order to succeed — matching a substring is not enough. If any operand is NULL, the result is NULL. Otherwise, the result is TRUE or FALSE.

      Syntax: SQL Regular Expressions

      The following syntax defines the SQL regular expression format. It is a complete and correct top-down definition. It is also highly formal, rather long and probably perfectly fit to discourage everybody who hasn’t already some experience with regular expressions (or with highly formal, rather long top-down definitions). Feel free to skip it and read the next section, , which uses a bottom-up approach, aimed at the rest of us.

      1. <regular expression> ::= <regular term> ['|' <regular term> ...]
      3. <regular term> ::= <regular factor> ...
      5. <regular factor> ::= <regular primary> [<quantifier>]
      7. <quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'
      9. <m>, <n> ::= unsigned int, with <m> <= <n> if both present
      11. <regular primary> ::=
      12.    <character> | <character class> | %
      13.   | (<regular expression>)
      15. <character> ::= <escaped character> | <non-escaped character>
      17. <escaped character> ::=
      18.   <escape-char> <special character> | <escape-char> <escape-char>
      20. <special character> ::= any of the characters []()|^-+*%?{_
      22. <non-escaped character> ::=
      23.   any character that is not a <special character>
      24.   and not equal to <escape-char> (if defined)
      26. <character class> ::=
      27.     '_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
      28.   | '[' <member> ... '^' <non-member> ... ']'
      30. <member>, <non-member> ::= <character> | <range> | <predefined class>
      32. <range> ::= <character>-<character>
      34. <predefined class> ::= '[:' <predefined class name> ':]'
      36. <predefined class name> ::=
      37.   ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE

      Building Regular Expressions

      In this section are the elements and rules for building SQL regular expressions.

      Characters

      Within regular expressions, most characters represent themselves. The only exceptions are the special characters below:

      1. [ ] ( ) | ^ - + * % _ ? { }

      … and the escape character, if it is defined.

      A regular expression that contains no special or escape characters matches only strings that are identical to itself (subject to the collation in use). That is, it functions just like the ‘=’ operator:

      1. 'Apple' similar to 'Apple'  -- true
      2. 'Apples' similar to 'Apple' -- false
      3. 'Apple' similar to 'Apples' -- false
      4. 'APPLE' similar to 'Apple'  -- depends on collation

      Wildcards

      The known SQL wildcards ‘_’ and ‘%’ match any single character and a string of any length, respectively:

      1. 'Birne' similar to 'B_rne'   -- true
      2. 'Birne' similar to 'B_ne'    -- false
      3. 'Birne' similar to 'B%ne'    -- true
      4. 'Birne' similar to 'Bir%ne%' -- true

      Notice how ‘%’ also matches the empty string.

      Character Classes

      A bunch of characters enclosed in brackets define a character class. A character in the string matches a class in the pattern if the character is a member of the class:

      1. 'Citroen' similar to 'Cit[arju]oen'     -- true
      2. 'Citroen' similar to 'Ci[tr]oen'        -- false
      3. 'Citroen' similar to 'Ci[tr][tr]oen'    -- true

      As can be seen from the second line, the class only matches a single character, not a sequence.

      Within a class definition, two characters connected by a hyphen define a range. A range comprises the two endpoints and all the characters that lie between them in the active collation. Ranges can be placed anywhere in the class definition without special delimiters to keep them apart from the other elements.

      1. 'Datte' similar to 'Dat[q-u]e'          -- true
      2. 'Datte' similar to 'Dat[abq-uy]e'       -- true
      3. 'Datte' similar to 'Dat[bcg-km-pwz]e'   -- false

      Predefined Character Classes

      The following predefined character classes can also be used in a class definition:

      Latin letters a..z and A..Z. With an accent-insensitive collation, this class also matches accented forms of these characters.

      [:DIGIT:]

      [:ALNUM:]

      Union of [:ALPHA:] and [:DIGIT:].

      [:UPPER:]

      Uppercase Latin letters A..Z. Also matches lowercase with case-insensitive collation and accented forms with accent-insensitive collation.

      [:LOWER:]

      Lowercase Latin letters a..z. Also matches uppercase with case-insensitive collation and accented forms with accent-insensitive collation.

      [:SPACE:]

      Matches the space character (ASCII 32).

      [:WHITESPACE:]

      Matches horizontal tab (ASCII 9), linefeed (ASCII 10), vertical tab (ASCII 11), formfeed (ASCII 12), carriage return (ASCII 13) and space (ASCII 32).

      Including a predefined class has the same effect as including all its members. Predefined classes are only allowed within class definitions. If you need to match against a predefined class and nothing more, place an extra pair of brackets around it.

      1. 'Erdbeere' similar to 'Erd[[:ALNUM:]]eere'     -- true
      2. 'Erdbeere' similar to 'Erd[[:DIGIT:]]eere'     -- false
      3. 'Erdbeere' similar to 'Erd[a[:SPACE:]b]eere'   -- true
      4. 'Erdbeere' similar to [[:ALPHA:]]              -- false
      5. 'E'        similar to [[:ALPHA:]]              -- true

      If a class definition starts with a caret, everything that follows is excluded from the class. All other characters match:

      If the caret is not placed at the start of the sequence, the class contains everything before the caret, except for the elements that also occur after the caret:

      1. 'Grapefruit' similar to 'Grap[a-m^f-i]fruit'   -- true
      2. 'Grapefruit' similar to 'Grap[abc^xyz]fruit'   -- false
      3. 'Grapefruit' similar to 'Grap[abc^de]fruit'    -- false
      4. 'Grapefruit' similar to 'Grap[abe^de]fruit'    -- false
      6. '3' similar to '[[:DIGIT:]^4-8]'               -- true
      7. '6' similar to '[[:DIGIT:]^4-8]'               -- false

      Lastly, the already mentioned wildcard ‘_’ is a character class of its own, matching any single character.

      Quantifiers

      A question mark (‘?’) immediately following a character or class indicates that the preceding item may occur 0 or 1 times in order to match:

      1. 'Hallon' similar to 'Hal?on'                   -- false
      2. 'Hallon' similar to 'Hal?lon'                  -- true
      3. 'Hallon' similar to 'Halll?on'                 -- true
      4. 'Hallon' similar to 'Hallll?on'                -- false
      5. 'Hallon' similar to 'Halx?lon'                 -- true
      6. 'Hallon' similar to 'H[a-c]?llon[x-z]?'        -- true

      An asterisk (‘*’) immediately following a character or class indicates that the preceding item may occur 0 or more times in order to match:

      1. 'Icaque' similar to 'Ica*que'                  -- true
      2. 'Icaque' similar to 'Icar*que'                 -- true
      3. 'Icaque' similar to 'I[a-c]*que'               -- true
      4. 'Icaque' similar to '_*'                       -- true
      5. 'Icaque' similar to '[[:ALPHA:]]*'             -- true
      6. 'Icaque' similar to 'Ica[xyz]*e'               -- false

      A plus sign (‘+’) immediately following a character or class indicates that the preceding item must occur 1 or more times in order to match:

      1. 'Jujube' similar to 'Ju_+'                     -- true
      2. 'Jujube' similar to 'Ju+jube'                  -- true
      3. 'Jujube' similar to 'Jujuber+'                 -- false
      4. 'Jujube' similar to 'J[jux]+be'                -- true
      5. 'Jujube' sililar to 'J[[:DIGIT:]]+ujube'       -- false

      If a character or class is followed by a number enclosed in braces (‘{’ and ‘}’), it must be repeated exactly that number of times in order to match:

      1. 'Kiwi' similar to 'Ki{2}wi'                    -- false
      2. 'Kiwi' similar to 'K[ipw]{2}i'                 -- true
      3. 'Kiwi' similar to 'K[ipw]{2}'                  -- false
      4. 'Kiwi' similar to 'K[ipw]{3}'                  -- true

      If the number is followed by a comma (‘,’), the item must be repeated at least that number of times in order to match:

      1. 'Limone' similar to 'Li{2,}mone'               -- false
      2. 'Limone' similar to 'Li{1,}mone'               -- true
      3. 'Limone' similar to 'Li[nezom]{2,}'            -- true

      If the braces contain two numbers separated by a comma, the second number not smaller than the first, then the item must be repeated at least the first number and at most the second number of times in order to match:

      1. 'Mandarijn' similar to 'M[a-p]{2,5}rijn'       -- true
      2. 'Mandarijn' similar to 'M[a-p]{2,3}rijn'       -- false
      3. 'Mandarijn' similar to 'M[a-p]{2,3}arijn'      -- true

      The quantifiers ‘?’, ‘*’ and ‘+’ are shorthand for {0,1}, {0,} and {1,}, respectively.

      OR-ing Terms

      Regular expression terms can be OR’ed with the ‘|’ operator. A match is made when the argument string matches at least one of the terms:

      1. 'Nektarin' similar to 'Nek|tarin'              -- false
      2. 'Nektarin' similar to 'Nektarin|Persika'       -- true
      3. 'Nektarin' similar to 'M_+|N_+|P_+'            -- true

      Subexpressions

      One or more parts of the regular expression can be grouped into subexpressions (also called subpatterns) by placing them between parentheses (‘(’ and ‘)’). A subexpression is a regular expression in its own right. It can contain all the elements allowed in a regular expression, and can also have quantifiers added to it.

      1. 'Orange' similar to 'O(ra|ri|ro)nge'           -- true
      2. 'Orange' similar to 'O(r[a-e])+nge'            -- true
      3. 'Orange' similar to 'O(ra){2,4}nge'            -- false
      4. 'Orange' similar to 'O(r(an|in)g|rong)?e'      -- true

      Escaping Special Characters

      In order to match against a character that is special in regular expressions, that character has to be escaped. There is no default escape character; rather, the user specifies one when needed:

      1. 'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\'    -- true
      2. 'Pera [Pear]'  similar to 'P[^ ]+ #[P[^ ]+#]' escape '#'    -- true
      3. 'Päron-äppledryck' similar to 'P%$-ä%' escape '$'           -- true
      4. 'Pärondryck' similar to 'P%--ä%' escape '-'                 -- false

      The last line demonstrates that the escape character can also escape itself, if needed.

      IS [NOT] DISTINCT FROM

      Available

      DSQL, PSQL

      Syntax

      1. <operand1> IS [NOT] DISTINCT FROM <operand2>

      Two operands are considered DISTINCT if they have a different value or if one of them is NULL and the other non-null. They are NOT DISTINCT if they have the same value or if both of them are NULL.

      See also

      IS [NOT] NULL

      Available

      DSQL, PSQL, ESQL

      Syntax

      1. <value> IS [NOT] NULL

      Since NULL is not a value, these operators are not comparison operators. The IS [NOT] NULL predicate tests the assertion that the expression on the left side has a value (IS NOT NULL) or has no value (IS NULL).

      Example

      Search for sales entries that have no shipment date set for them: