The Firebird 5.0 Language Reference | Character Data Types

Collation

Each character set has a default collation (COLLATE) that specifies the collation order (or, collation sequence, or collating sequence).Usually, it provides nothing more than ordering based on the numeric code of the characters and a basic mapping of upper- and lower-case characters.If some behaviour is needed for strings that is not provided by the default collation and a suitable alternative collation is supported for that character set, a COLLATE collation clause can be specified in the column definition.

A COLLATE collation clause can be applied in other contexts besides the column definition.For comparison operations, it can be added in the WHERE clause of a SELECT statement.If output needs to be sorted in a special alphabetic sequence, or case-insensitively, and the appropriate collation exists, then a COLLATE clause can be included with the ORDER BY clause when rows are being sorted on a character field and with the GROUP BY clause in case of grouping operations.

Case-Insensitive Searching

For a case-insensitive search, the UPPER function could be used to convert both the search argument and the searched strings to upper-case before attempting a match:

...
where upper(name) = upper(:flt_name)

For strings in a character set that has a case-insensitive collation available, you can apply the collation, to compare the search argument and the searched strings directly.For example, using the WIN1251 character set, the collation PXW_CYRL is case-insensitive for this purpose:

...
WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME
...
ORDER BY NAME COLLATE PXW_CYRL

See also

CONTAINING

`UTF8` Collations

The following table shows the possible collations for the UTF8 character set.

Table 1. Collations for Character Set UTF8
Collation	Characteristics
`UCS_BASIC`	Collation works according to the position of the character in the table (binary).
`UNICODE`	Collation works according to the UCA algorithm (Unicode Collation Algorithm) (alphabetical).
`UTF8`	The default, binary collation, identical to `UCS_BASIC`, which was added for SQL compatibility
`UNICODE_CI`	Case-insensitive collation, works without taking character case into account.
`UNICODE_CI_AI`	Case-insensitive, accent-insensitive collation, works alphabetically without taking character case or accents into account.

Example

An example of collation for the UTF8 character set without taking into account the case or accentuation of characters (similar to COLLATE PXW_CYRL in the earlier example).

...
ORDER BY NAME COLLATE UNICODE_CI_AI

Character Indexes

The maximum length for an index key equals one quarter of the page size, i.e. from 1,024 — for page size 4,096 — to 8,192 bytes — for page size 32,768.The maximum length of an indexed string is 9 bytes less than that quarter-page limit.

Calculating Maximum Length of an Indexed String Field

The following formula calculates the maximum length of an indexed string (in characters):

max_char_length = FLOOR((page_size / 4 - 9) / N)

where N is the number of bytes per character in the character set.

The table below shows the maximum length of an indexed string (in characters), according to page size and character set, calculated using this formula.

Table 1. Maximum Index Lengths by Page Size and Character Size
Page Size	Bytes per character
Page Size	1	2	3	4	6
4,096	1,015	507	338	253	169
8,192	2,039	1,019	679	509	339
16,384	4,087	2,043	1,362	1,021	681
32,768	8,183	4,091	2,727	2,045	1,363

Note	With case-insensitive collations (“_CI”), one character in the index key will occupy not 4, but 6 (six) bytes, so the maximum key length for a page of — for example — 4,096 bytes, will be 169 characters.

Character Types in Detail

`BINARY`

Data Type Declaration Format

BINARY [(length)]

Table 1. `BINARY` Type Parameters
Parameter	Description
length	Length in bytes between 1 and 32,767;defaults to `1`.

BINARY is a fixed-length binary data type, and is an SQL standard-compliant alias for CHAR(length) CHARACTER SET OCTETS.Values shorter than the declared length are padded with NUL (0x00) up to the declared length.

Note	Some tools may report the type as `CHAR CHARACTER SET OCTETS` instead of `BINARY`.

`CHAR`

Data Type Declaration Format

{CHAR | CHARACTER} [(length)]
  [CHARACTER SET <set>] [COLLATE <name>]

Table 1. `CHAR` Type Parameters
Parameter	Description
length	Length in characters, defaults to `1`.A valid length is from 1 to the maximum number of characters that can be accommodated within 32,767 bytes.
set	Character set name
name	Collation name

CHAR is a fixed-length character data type.Values shorter than the declared length are padded with spaces up to the declared length.The pad character does not have to be a space (0x20): it depends on the character set.For example, the pad character for the OCTETS character set is NUL (0x00).

Fixed-length character data can be used to store codes whose length is standard and has a definite “width”.An example of such a code is an EAN13 barcode — 13 characters, all filled.

Note	`CHAR CHARACTER SET OCTETS` has the alias `BINARY` Formally, the `COLLATE` clause is not part of the data type declaration, and its position depends on the syntax of the statement.

`VARBINARY`

Data Type Declaration Format

{VARBINARY | BINARY VARYING} (length)

Table 1. `VARBINARY` Type Parameters
Parameter	Description
length	Length in bytes between 1 and 32,765

VARBINARY is a variable-length binary type, and is an SQL standard-compliant alias for VARCHAR(length) CHARACTER SET OCTETS.

Note	Some tools may report the type as `VARCHAR CHARACTER SET OCTETS` instead of `VARBINARY`.

`VARCHAR`

Data Type Declaration Format

{VARCHAR | {CHAR | CHARACTER} VARYING} (length)
  [CHARACTER SET <set>] [COLLATE <name>]

Table 1. `VARCHAR` Type Parameters
Parameter	Description
length	Length in characters.A valid length is from 1 to the maximum number of characters that can be accommodated within 32,765 bytes.
set	Character set name
name	Collation name

VARCHAR is a variable-length character data type, up to a maximum of 32,765 bytes.The stored structure is equal to the actual size of the data plus 2 bytes to record the length of the data.

All characters that are sent from the client application to the database are considered meaningful, including leading and trailing spaces.

Note	`VARCHAR CHARACTER SET OCTETS` has the alias `VARBINARY` Formally, the `COLLATE` clause is not part of the data type declaration, and its position depends on the syntax of the statement.

`NCHAR`

Data Type Declaration Format

{NCHAR | NATIONAL {CHAR | CHARACTER}} [(length)]

NCHAR is a fixed-length character data type with the ISO8859_1 character set.In all other respects it is the same as CHAR.

Note	If no length is specified, it is taken to be 1.

A similar data type is available for the variable-length string type: NATIONAL {CHAR | CHARACTER} VARYING.

Boolean Data Type

`BOOLEAN`

Data Type Declaration Format

BOOLEAN

The SQL-compliant BOOLEAN data type (8 bits) comprises the distinct truth values TRUE and FALSE.Unless prohibited by a NOT NULL constraint, the BOOLEAN data type also supports the truth value UNKNOWN as the null value.The specification does not make a distinction between the NULL value of this data type, and the truth value UNKNOWN that is the result of an SQL predicate, search condition, or Boolean value expression: they may be used interchangeably to mean the same thing.

As with many programming languages, the SQL BOOLEAN values can be tested with implicit truth values.For example, field1 OR field2 and NOT field1 are valid expressions.

The `IS` Operator

Predicates can use the operator Boolean IS [NOT] for matching.For example, field1 IS FALSE, or field1 IS NOT TRUE.

Note	Equivalence operators (“`=`”, “`!=`”, “`<>`” and so on) are valid in all comparisons.

`BOOLEAN` Examples

Inserting and selecting

CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
COMMIT;

INSERT INTO TBOOL VALUES (1, TRUE);
INSERT INTO TBOOL VALUES (2, 2 = 4);
INSERT INTO TBOOL VALUES (3, NULL = 1);
COMMIT;

SELECT * FROM TBOOL;
          ID    BVAL
============ =======
           1 <true>
           2 <false>
           3 <null>

Test for TRUE value

SELECT * FROM TBOOL WHERE BVAL;
          ID    BVAL
============ =======
           1 <true>

Test for FALSE value

SELECT * FROM TBOOL WHERE BVAL IS FALSE;
          ID    BVAL
============ =======
           2 <false>

Test for UNKNOWN value

SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
          ID    BVAL
============ =======
           3 <null>

Boolean values in SELECT list

SELECT ID, BVAL, BVAL AND ID < 2
  FROM TBOOL;
          ID    BVAL
============ ======= =======
           1 <true>  <true>
           2 <false> <false>
           3 <null>  <false>

PSQL declaration with start value
```
DECLARE VARIABLE VAR1 BOOLEAN = TRUE;
```
Valid syntax, but as with a comparison with NULL, will never return any record

SELECT * FROM TBOOL WHERE BVAL = UNKNOWN;
SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN;

Use of Boolean Against Other Data Types

Although BOOLEAN is not inherently convertible to any other data type, the strings 'true' and 'false' (case-insensitive) will be implicitly cast to BOOLEAN in value expressions.For example:

if (true > 'false') then ...

The value 'false' is converted to BOOLEAN.Any attempt to use the Boolean operators AND, NOT, OR and IS will fail.NOT 'False', for example, is invalid.

A BOOLEAN can be explicitly converted to and from string with CAST.UNKNOWN is not available for any form of casting.

Note	Other Notes The type is represented in the API with the `FB_BOOLEAN` type and `FB_TRUE` and `FB_FALSE` constants. The value `TRUE` is greater than the value `FALSE`.

Binary Data Types

Note	The types [fblangref50-datatypes-chartypes-binary] and [fblangref50-datatypes-chartypes-varbinary] are covered earlier in section [fblangref50-datatypes-chartypes].

BLOBs (Binary Large Objects) are complex structures used to store text and binary data of an undefined length, often very large.

Syntax

BLOB [SUB_TYPE <subtype>]
  [SEGMENT SIZE <segment size>]
  [CHARACTER SET <character set>]
  [COLLATE <collation name>]

If the SUB_TYPE and CHARACTER SET clauses are absent, then subtype BINARY (or 0) is used.If the SUB_TYPE clause is absent and the CHARACTER SET clause is present, then subtype TEXT (or 1) is used.

Shortened syntax

BLOB (<segment size>)
BLOB (<segment size>, <subtype>)
BLOB (, <subtype>)

Note	Formally, the `COLLATE` clause is not part of the data type declaration, and its position depends on the syntax of the statement.

Segment Size

Specifying the BLOB segment size is a throwback to times past, when applications for working with BLOB data were written in C (Embedded SQL) with the help of the gpre pre-compiler.Nowadays, it is effectively irrelevant.The segment size for BLOB data is determined by the client side and is usually larger than the data page size, in any case.

`BLOB` Subtypes

The optional SUB_TYPE parameter specifies the nature of data written to the column.Firebird provides two pre-defined subtypes for storing user data:

Subtype 0: BINARY: If a subtype is not specified, the specification is assumed to be for untyped data and the default SUB_TYPE BINARY (or SUB_TYPE 0) is applied.This is the subtype to specify when the data are any form of binary file or stream: images, audio, word-processor files, PDFs and so on.
Subtype 1: TEXT: Subtype 1 has an alias, TEXT, which can be used in declarations and definitions.For instance, BLOB SUB_TYPE TEXT (or BLOB SUB_TYPE 1).It is a specialized subtype used to store plain text data that is too large to fit into a string type.A CHARACTER SET may be specified, if the field is to store text with a different encoding to that specified for the database.A COLLATE clause is also supported.

Specifying CHARACTER SET without specifying a SUB_TYPE implies SUB_TYPE TEXT.

Custom Subtypes

It is also possible to add custom data subtypes, for which the range of enumeration from -1 to -32,768 is reserved.Custom subtypes enumerated with positive numbers are not allowed, as the Firebird engine uses the numbers from 2-upward for some internal subtypes in metadata.Custom subtype aliases can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.

`BLOB` Specifics

Size

The maximum size of a BLOB field depends on the page size of the database, whether the blob value is created as a stream blob or a segmented blob, and if segmented, the actual segment sizes used when populating the blob.For most built-in functions, the maximum size of a BLOB field is 4 GB, or data beyond the 4 GB limit is not addressable.For a page size of 4 KB (4096 bytes) the maximum size is slightly less than 4 GB.

Operations and Expressions

Text BLOBs of any length and any character set — including multi-byte — can be operands for practically any statement or internal functions.The following operators are fully supported:

=

(assignment)

=, <>, <, <=, >, >=

(comparison)

||

(concatenation)

BETWEEN,

IS [NOT] DISTINCT FROM,

IN,

ANY | SOME,

ALL

As an efficient alternative to concatenation, you can also use BLOB_APPEND() or the functions and procedures of system package RDB$BLOB_UTIL.

Partial support:

An error occurs with these if the search argument is larger than or equal to 32 KB:

STARTING [WITH],

LIKE,

CONTAINING

Aggregation clauses work not on the contents of the field itself, but on the BLOB ID.Aside from that, there are some quirks:

SELECT DISTINCT

returns several NULL values by mistake if they are present

ORDER BY

—

GROUP BY

concatenates the same strings if they are adjacent to each other, but does not do it if they are remote from each other

BLOB Storage

By default, a regular record is created for each BLOB, and it is stored on a data page that is allocated for it.If the entire BLOB fits onto this page, it is called a level 0 BLOB.The number of this special record is stored in the table record and occupies 8 bytes.
If a BLOB does not fit onto one data page, its contents are put onto separate pages allocated exclusively to it (blob pages), while the numbers of these pages are stored into the BLOB record.This is a level 1 BLOB.
If the array of page numbers containing the BLOB data does not fit onto a data page, the array is put on separate blob pages, while the numbers of these pages are put into the BLOB record.This is a level 2 BLOB.
Levels higher than 2 are not supported.

Array Types

Note	Firebird does not offer much in the way of language or tools for working with the contents of arrays, and there are no plans to improve this.This limits the usefulness and accessibility of array types.Therefore, the general advice is: do not use arrays.

The support of arrays in the Firebird DBMS is a departure from the traditional relational model.Supporting arrays in the DBMS could make it easier to solve some data-processing tasks involving large sets of similar data.

Arrays in Firebird are stored in BLOB of a specialized type.Arrays can be one-dimensional and multi-dimensional and of any data type except BLOB and ARRAY.

Example

CREATE TABLE SAMPLE_ARR (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [4]
);

This example will create a table with a field of the array type consisting of four integers.The subscripts of this array are from 1 to 4.

Specifying Explicit Boundaries for Dimensions

By default, dimensions are 1-based — subscripts are numbered from 1.To specify explicit upper and lower bounds of the subscript values, use the following syntax:

'[' <lower>:<upper> ']'

Adding More Dimensions

A new dimension is added using a comma in the syntax.In this example we create a table with a two-dimensional array, with the lower bound of subscripts in both dimensions starting from zero:

CREATE TABLE SAMPLE_ARR2 (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [0:3, 0:3]
);

The database employee.fdb, found in the ../examples/empbuild directory of any Firebird distribution package, contains a sample stored procedure showing some simple work with arrays:

PSQL Source for `SHOW_LANGS`, a procedure involving an array

CREATE OR ALTER PROCEDURE SHOW_LANGS (
  CODE VARCHAR(5),
  GRADE SMALLINT,
  CTY VARCHAR(15))
RETURNS (LANGUAGES VARCHAR(15))
AS
  DECLARE VARIABLE I INTEGER;
BEGIN
  I = 1;
  WHILE (I <= 5) DO
  BEGIN
    SELECT LANGUAGE_REQ[:I]
    FROM JOB
    WHERE (JOB_CODE = :CODE)
      AND (JOB_GRADE = :GRADE)
      AND (JOB_COUNTRY = :CTY)
      AND (LANGUAGE_REQ IS NOT NULL))
    INTO :LANGUAGES;

    IF (LANGUAGES = '') THEN
    /* PRINTS 'NULL' INSTEAD OF BLANKS */
      LANGUAGES = 'NULL';
    I = I +1;
    SUSPEND;
  END
END

If the features described are enough for your tasks, you might consider using arrays in your projects.Currently, no improvements are planned to enhance support for arrays in Firebird.

Special Data Types

“Special” data types …

`SQL_NULL` Data Type

The SQL_NULL type holds no data, but only a state: NULL or NOT NULL.It is not available as a data type for declaring table fields, PSQL variables or parameter descriptions.This data type exists to support the use of untyped parameters in expressions involving the IS NULL predicate.

An evaluation problem occurs when optional filters are used to write queries of the following type:

WHERE col1 = :param1 OR :param1 IS NULL

After processing, at the API level, the query will look like this:

WHERE col1 = ? OR ? IS NULL

This is a case where the developer writes an SQL query and considers :param1 as though it were a variable that they can refer to twice.However, at the API level, the query contains two separate and independent parameters.The server cannot determine the type of the second parameter since it comes in association with IS NULL.

The SQL_NULL data type solves this problem.Whenever the engine encounters an “? IS NULL” predicate in a query, it assigns the SQL_NULL type to the parameter, which will indicate that parameter is only about “nullness” and the data type or the value need not be addressed.

The following example demonstrates its use in practice.It assumes two named parameters — say, :size and :colour — which might, for example, get values from on-screen text fields or drop-down lists.Each named parameter corresponds with two positional parameters in the query.

SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)

Explaining what happens here assumes the reader is familiar with the Firebird API and the passing of parameters in XSQLVAR structures — what happens under the surface will not be of interest to those who are not writing drivers or applications that communicate using the “naked” API.

The application passes the parameterized query to the server in the usual positional ?-form.Pairs of “identical” parameters cannot be merged into one, so for the two optional filters in the example, four positional parameters are needed: one for each ? in our example.

After the call to isc_dsql_describe_bind(), the SQLTYPE of the second and fourth parameters will be set to SQL_NULL.Firebird has no knowledge of their special relation with the first and third parameters: that responsibility lies entirely on the application side.

Once the values for size and colour have been set (or left unset) by the user, and the query is about to be executed, each pair of XSQLVARs must be filled as follows:

User has supplied a value: First parameter (value compare): set *sqldata to the supplied value and *sqlind to 0 (for NOT NULL)

Second parameter (NULL test): set sqldata to null (null pointer, not SQL NULL) and *sqlind to 0 (for NOT NULL)
User has left the field blank: Both parameters: set sqldata to null (null pointer, not SQL NULL) and *sqlind to -1 (indicating NULL)

In other words: The value compare parameter is always set as usual.The SQL_NULL parameter is set the same, except that sqldata remains null at all times.

Collation

Collation

Case-Insensitive Searching

docnext count = 21

UTF8 Collations

Character Indexes

Character Types in Detail

BINARY

CHAR

VARBINARY

VARCHAR

NCHAR

Boolean Data Type

BOOLEAN

The IS Operator

BOOLEAN Examples

Use of Boolean Against Other Data Types

Binary Data Types

BLOB Subtypes

BLOB Specifics

Array Types

Specifying Explicit Boundaries for Dimensions

Adding More Dimensions

PSQL Source for SHOW_LANGS, a procedure involving an array

Special Data Types

SQL_NULL Data Type

`UTF8` Collations

`BINARY`

`CHAR`

`VARBINARY`

`VARCHAR`

`NCHAR`

`BOOLEAN`

The `IS` Operator

`BOOLEAN` Examples

`BLOB` Subtypes

`BLOB` Specifics

PSQL Source for `SHOW_LANGS`, a procedure involving an array

`SQL_NULL` Data Type