FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

String Literals

A string literal is a series of characters enclosed between a pair of apostrophes (“single quotes”).The maximum length of a string literal is 32,765 for CHAR/VARCHAR, or 65,533 bytes for BLOB;the maximum character count will be determined by the number of bytes used to encode each character.

Formally, the syntax of a normal string literal is:

Character String Literal Syntax
<char-literal> ::=
  [<introducer> charset-name] <quote> [<char>...] <quote>
  [{ <separator> <quote> [<char>...] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }

<introducer> ::= underscore (U+005F)
<quote> ::= apostrophe (U+0027)
<char> ::= character representation;
           apostrophe is escaped by doubling
Note

  • Double quotes are not valid for quoting strings.The SQL standard reserves double quotes for a different purpose: delimiting or quoting identifiers.

  • If a literal apostrophe is required within a string constant, it is “escaped” by prefixing it with another apostrophe.For example, 'Mother O''Reilly''s home-made hooch'.Or use the alternative quote literal: q'{Mother O'Reilly's home-made hooch}'

  • Care should be taken with the string length if the value is to be written to a CHAR or VARCHAR column.The maximum length for a CHAR or VARCHAR literal is 32,765 bytes.

The character set of a string constant is assumed to be the same as the character set of its destined storage.

Examples
-- Literal containing single quote
select 'O''Reilly' from RDB$DATABASE;
-- output: O'Reilly
-- whitespace between literal
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: abcd
-- comment and whitespace between literal
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: abcd
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.