FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
String Literals in Hexadecimal Notation

String literals can also 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 be type BINARY (a.k.a. CHAR CHARACTER SET OCTETS) by default, unless the introducer syntax is used to force a string to be interpreted as another character set.

Binary String Literal Syntax
<binary-literal> ::=
  [<introducer> charsetname] X <quote> [<space>...]
  [{ <hexit> [<space>...] <hexit> [<space>...] }...] <quote>
  [{ <separator> <quote> [<space>...]
     [{ <hexit> [<space>...] <hexit> [<space>...] }...] <quote> }...]

<hexdigit> ::= one of 0..9, A..F, a..f
<space> ::= the space character (U+0020)

!! For further rules, see [fblangref50-char-literal-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)

-- Group per byte (whitespace inside literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY

-- whitespace between literal
select _win1252 x'42494e'
                 '415259'
from RDB$DATABASE;
-- output: BINARY
Note
Notes

The client interface determines how binary strings are displayed to the user.The isql utility, for example, uses upper case letters A-F, while FlameRobin uses lower case letters.Other client programs may use other conventions, such as displaying spaces between the byte pairs: '4E 65 72 76 65 6E'.

The hexadecimal notation allows any byte value (including 00) to be inserted at any position in the string.However, if you want to coerce it to anything other than OCTETS, it is your responsibility to supply the bytes in a sequence that is valid for the target character set.

The usage of the _win1252 introducer in above example is a non-standard extension and equivalent to an explicit cast to a CHAR of appropriate length with character set WIN1252.
Alternative String Literals

It is possible to use a character, or character pair, other than the doubled (escaped) apostrophe, to embed a quoted string inside another string without the need to escape the quote.The keyword q or Q preceding a quoted string informs the parser that certain left-right pairs or pairs of identical characters within the string are the delimiters of the embedded string literal.

Syntax
<alternative string literal> ::=
    { q | Q } <quote> <start char> [<char> ...] <end char> <quote>
Note
Rules

When <start char> is ‘(’, ‘{’, ‘[’ or ‘<’, <end char> is paired up with its respective “partner”, viz. ‘)’, ‘}’, ‘]’ and ‘>’.In other cases, <end char> is the same as <start char>.

Inside the string, i.e. <char> items, single quotes can be used without escaping.Each quote will be part of the result string.
Examples
select q'{abc{def}ghi}' from rdb$database;        -- result: abc{def}ghi
select q'!That's a string!' from rdb$database;    -- result: That's a string