Available in: DSQL, PSQL

Added in: 1.0

Changed in: 1.5

Description

A line starting with “- -” (two dashes) is a comment and will be ignored. This also makes it easy to quickly comment out a line of SQL.

In Firebird 1.5 and up, the “- -” can be placed anywhere on the line, e.g. after an SQL statement. Everything from the double dash to the end of the line will be ignored.

Example

a table to store our valued customers in:

create table Customers (
  name varchar(32),
  added_by varchar(24),
  custno varchar(8),
  purchases integer -- number of purchases
)

Notice that the second comment is only allowed in Firebird 1.5 and up.

back to top of page

Available in: DSQL, PSQL

Added in: 2.5

Description

In Firebird 2.5 and up, integer values can be entered in hexadecimal notation. Numbers with 1–8 hex digits will be interpreted as INTEGERs; numbers with 9–16 hex digits as BIGINTs.

Syntax

0{x|X}<hexdigits>
<hexdigits>    ::=   1–16 of <hexdigit>
<hexdigit>     ::=   one of 0..9, A..F, a..f

Examples

select 0x6FAA0D3 from rdb$database           -- returns 117088467
select 0x4F9 from rdb$database               -- returns 1273
select 0x6E44F9A8 from rdb$database          -- returns 1850014120
select 0x9E44F9A8 from rdb$database          -- returns -1639646808 (an INTEGER)
select 0x09E44F9A8 from rdb$database         -- returns 2655320488 (a BIGINT)
select 0x28ED678A4C987 from rdb$database     -- returns 720001751632263
select 0xFFFFFFFFFFFFFFFF from rdb$database  -- returns -1

Value ranges

• Hex numbers in the range 0 .. 7FFF FFFF are positive INTEGERs with values between 0 .. 2147483647 decimal. You can force them to BIGINT by prepending enough zeroes to bring the total number of hex digits to nine or above, but that only changes their type, not their value.
• Hex numbers between 8000 0000 .. FFFF FFFF require some attention:
  • When written with eight hex digits, as in 0x9E44F9A8, they are interpreted as 32-bit INTEGER values. Since their leftmost bit (sign bit) is set, they map to the negative range -2147483648 .. -1 decimal.
  • With one or more zeroes prepended, as in 0x09E44F9A8, they are interpreted as 64-bit BIGINTs in the range 0000 0000 8000 0000 .. 0000 0000 FFFF FFFF. The sign bit isn't set now, so they map to the positive range 2147483648 .. 4294967295 decimal.

Thus, in this range – and in this range only – prepending a mathematically insignificant 0 results in a totally different value. This is something to be aware of.

• Hex numbers between 1 0000 0000 .. 7FFF FFFF FFFF FFFF are all positive BIGINTs.
• Hex numbers between 8000 0000 0000 0000 .. FFFF FFFF FFFF FFFF are all negative BIGINTs.

back to top of page

Available in: DSQL, PSQL

Added in: 2.5

Description

In Firebird 2.5 and up, string literals can be entered in hexadecimal notation. Each pair of hex digits defines a byte in the string. Strings entered this way will have character set OCTETS by default, but you can force the engine to interpret them otherwise with the introducer syntax.

Syntax

{x|X}'<hexstring>'
<hexstring>     ::= an even number of <hexdigit>
<hexdigit>      ::= one of 0..9, A..F, a..f

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)

Notes:

• It is up to the client interface how binary strings are displayed to the user. Isql, for one, uses uppercase letters A-F. FlameRobin uses lowercase letters. Other client programs may have other ideas, e.g. like this, with spaces between the bytes: '4E 65 72 76 65 6E'.
• The hexadecimal notation allows you to insert any byte value (including 00) at any place in the string. However, if you want to coerce it to anything other than OCTETS, it is your responsibilty that the byte sequence is valid for the target character set.

back to top of page

Available in: DSQL, PSQL, ESQL

Added in: IB

Description

When converting a string literal to a DATE, TIME or TIMESTAMP, Firebird allows the use of a shorthand “C-style” cast. This feature already existed in InterBase 6, but was never properly documented.

Syntax

datatype 'date/timestring'

Examples

update People set AgeCat = 'Old'
  where BirthDate < date '1-Jan-1943'

insert into Appointments
  (Employee_Id, Client_Id, App_date, App_time)
values
 (973, 8804, date 'today' + 2, time '16:00')

new.lastmod = timestamp 'now';

See also: CAST?

back to top of page

Available in: DSQL, PSQL

Added in: 1.5

Description

A CASE construct returns exactly one value from a number of possibilities. There are two syntactic variants:

• The simple CASE, comparable to a Pascal case or a C switch.
• The searched CASE, which works like a series of if … else if … else if clauses.

back to top of page

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, <expr> 2 etc., until a match is found, upon which the corresponding result is returned. If there is no match and there is an ELSE clause, defaultresult is returned. If there is no match and no ELSE clause, NULL is returned.

The match is determined with the “=” operator, so if <test-expr> is NULL, it won't match any of the <expr>s, not even those that are NULL.

The results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

A shorthand form of the simple CASE construct is the DECODE() function, available since Firebird 2.1.

Example

select name,
       age,
       case upper(sex)
         when 'M' then 'Male'
         when 'F' then 'Female'
         else 'Unknown'
       end,
       religion
from people

back to top of page

Syntax

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

Here, the <bool_expr>s are tests that give a ternary Boolean result: TRUE, FALSE, or NULL. The first expression evaluating to TRUE determines the result. If no expression is TRUE and there is an ELSE clause, defaultresult is returned. If no expression is TRUE and there is no ELSE clause, NULL is returned.

As with the simple CASE, the results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

Example

CanVote = case
            when Age >= 18 then 'Yes'
            when Age < 18 then 'No'
            else 'Unsure'
          end;