FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
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
Introducer Syntax for String Literals

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

INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer')

Number Literals

A number literal is any valid number in a supported notation:

  • In SQL, for numbers in the standard decimal notation, the decimal point is always represented by period character (‘.’, full-stop, dot);thousands are not separated.Inclusion of commas, blanks, etc. will cause errors.

  • Exponential notation is supported.For example, 0.0000234 can be expressed as 2.34e-5.However, while the literal 0.0000234 is a NUMERIC(18,7), the literal 2.34e-5 is a DOUBLE PRECISION.

  • Hexadecimal notation — see below.

The format of the literal decides the type (<d> for a decimal digit, <h> for a hexadecimal digit):

Format Type

<d>[<d> …​]

INTEGER, BIGINT, INT128 or DECFLOAT(34) (depends on if value fits in the type).DECFLOAT(34) is used for values that do not fit in INT128.

0{x|X} <h>[<h> …​]

INTEGER for 1-8 <h>, or BIGINT for 9-16 <h>, INT128 for 17-32 <h>

<d>[<d> …​] "." [<d> …​]

NUMERIC(18, n), NUMERIC(38, n) or DECFLOAT(34) where n depends on the number of digits after the decimal point, and precision on the total number of digits.

For backwards compatibility, some values of 19 digits are mapped to NUMERIC(18, n).DECFLOAT(34) is used when the unscaled value does not fit in INT128.

<d>[<d> …​]["." [<d> …​]] E <d>[<d> …​]

DOUBLE PRECISION or DECFLOAT(34), where DECFLOAT is used only if the number of digits is 20 or higher, or the absolute exponent is 309 or greater.
Hexadecimal Notation for Numbers

Integer values can also 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;numbers with 17-32 hex digits as type INT128.

Syntax
0{x|X}<hexdigits>

<hexdigits>  ::=  1-32 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