Hexadecimal notation for “binary” strings

Tip

Find a more recent version at Firebird 5.0 Language Reference: String Literals in Hexadecimal Notation

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 responsibility that the byte sequence is valid for the target character set.