From Firebird 2.5 forward, string literals can be entered in hexadecimal notation, so-called “binary strings”. Each pair of hex digits defines one byte in the string. Strings entered this way will have character set OCTETS by default but the introducer syntax can be used to force a string to be interpreted as another character set.

Syntax: 

Examples: 

    select x'4E657276656E' from rdb$database
      -- returns 4E657276656E, a 6-byte 'binary' string

    select _ascii x'4E657276656E' from rdb$database
      -- returns 'Nerven' (same string, now interpreted as ASCII text)

    select _iso8859_1 x'53E46765' from rdb$database
      -- returns 'Säge' (4 chars, 4 bytes)

    select _utf8 x'53C3A46765' from rdb$database
      -- returns 'Säge' (4 chars, 5 bytes)
            

If necessary, a string literal may be preceded by a character set name, itself prefixed with an underscore “_”. This is known as introducer syntax. Its purpose is to inform the engine about how to interpret and store the incoming string.

Example

From Firebird 2.5 forward, integer values can be entered in hexadecimal notation. Numbers with 1-8 hex digits will be interpreted as type INTEGER; numbers with 9-16 hex digits as type BIGINT.

Syntax: 

Examples: 

The concatenation operator, two pipe characters known as “double pipe”— || — concatenates (connects together) two character strings to form a single string. Character strings can be constants or values obtained from columns or other expressions.

Example: 

       SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
       FROM EMPLOYEE
            

Example: 

       UPDATE T
         SET A = 4 + 1/(B-C)*D
            

This group also includes comparison predicates BETWEEN, LIKE, CONTAINING, SIMILAR TO, IS and others.

Example: 

       IF (SALARY > 1400) THEN
       …
            

See also: Other Comparison Predicates.

Example: 

       IF (A < B OR (A > C AND A > D) AND NOT (C = D)) THEN …
            

Syntax: 

       …
       CASE <test-expr>
         WHEN <expr> THEN <result>
         [WHEN <expr> THEN <result> ...]
         [ELSE <defaultresult>]
       END
       …
            

When this variant is used, <test-expr> is compared to <expr> 1, <exp> 2 etc., until a match is found and the corresponding result is returned. If no match is found, <defaultresult> from the optional ELSE clause is returned. If there are no matches and no ELSE clause, NULL is returned.

The matching works identically to the "=" operator. That is, if <test-expr> is NULL, it does not match any <expr>, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

Example: 

       SELECT
         NAME,
         AGE,
         CASE UPPER(SEX)
           WHEN 'M' THEN 'Male'
           WHEN 'F' THEN 'Female'
           ELSE 'Unknown'
         END GENDER,
	RELIGION
      FROM PEOPLE
              

A short form of the simple CASE construct is used in the DECODE function.

Syntax: 

       CASE
         WHEN <bool_expr> THEN <result>
         [WHEN <bool_expr> THEN <result> …]
         [ELSE <defaultresult>]
         END
            

The <bool_expr> expression is one that gives a ternary logical result: TRUE, FALSE or NULL. The first expression to return TRUE determines the result. If no expressions return TRUE, <defaultresult> from the optional ELSE clause is returned as the result. If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

Example: 

       CANVOTE = CASE
         WHEN AGE >= 18 THEN 'Yes'
         WHEN AGE < 18 THEN 'No'
         ELSE 'Unsure'
         END