FirebirdSQL logo
 Procedural SQL (PSQL) StatementsAggregate Functions 

BIT_LENGTH Examples

select bit_length('Hello!') from rdb$database
-- returns 48

select bit_length(_iso8859_1 'Grüß di!') from rdb$database
-- returns 64: ü and ß take up one byte each in ISO8859_1

select bit_length
  (cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
from rdb$database
-- returns 80: ü and ß take up two bytes each in UTF8

select bit_length
  (cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
from rdb$database
-- returns 208: all 24 CHAR positions count, and two of them are 16-bit
See also

[fblangref50-scalarfuncs-octet-length], [fblangref50-scalarfuncs-char-length]

BLOB_APPEND()

Efficient concatenation of blobs

Result type

BLOB

Syntax
BLOB_APPEND(expr1, expr2 [, exprN ... ])
Table 1. BLOB_APPEND Function Parameters
Parameter Description

exprN

An expression of a type convertible to BLOB

The BLOB_APPEND function concatenates blobs without creating intermediate BLOBs, avoiding excessive memory consumption and growth of the database file.The BLOB_APPEND function takes two or more arguments and adds them to a BLOB which remains open for further modification by a subsequent BLOB_APPEND call.

The resulting BLOB is left open for writing instead of being closed when the function returns.In other words, the BLOB can be appended as many times as required.The engine marks the BLOB returned by BLOB_APPEND with an internal flag, BLB_close_on_read, and closes it automatically when needed.

The first argument determines the behaviour of the function:

  1. NULL: new, empty BLOB SUB_TYPE TEXT is created, using the connection character set as the character set

  2. permanent BLOB (from a table) or temporary BLOB which was already closed: new BLOB is created with the same subtype and, if subtype is TEXT the same character set, populated with the content of the original BLOB.

  3. temporary unclosed BLOB with the BLB_close_on_read flag (e.g. created by another call to BLOB_APPEND): used as-is, remaining arguments are appended to this BLOB

  4. other data types: a new BLOB SUB_TYPE TEXT is created, populated with the original argument converted to string.If the original value is a character type, its character set is used (for string literals, the connection character set), otherwise the connection character set.

Other arguments can be of any type.The following behavior is defined for them:

  1. NULLs are ignored (behaves as empty string)

  2. BLOBs, if necessary, are transliterated to the character set of the first argument and their contents are appended to the result

  3. other data types are converted to strings (as usual) and appended to the result

The BLOB_APPEND function returns a temporary unclosed BLOB with the BLB_close_on_read flag.If the first argument is such a temporary unclosed BLOB (e.g. created by a previous call to BLOB_APPEND), it will be used as-is, otherwise a new BLOB is created.Thus, a series of operations like blob = BLOB_APPEND (blob, …​) will result in the creation of at most one BLOB (unless you try to append a BLOB to itself).This blob will be automatically closed by the engine when the client reads it, assigns it to a table, or uses it in other expressions that require reading the content.

Warning
Important caveats for BLOB_APPEND

  1. The NULL behaviour of BLOB_APPEND is different from normal concatenation (using ||).Occurrence of NULL will behave as if an empty string was used.In other words, NULL is effectively ignored.

    In normal concatenation, concatenating with NULL results in NULL.
Note

Testing a blob for NULL using the IS [NOT] NULL operator does not read it and therefore a temporary blob with the BLB_close_on_read flag will not be closed after such a test.
Tip

Use LIST or BLOB_APPEND functions to concatenate blobs.This reduces memory consumption and disk I/O, and also prevents database growth due to the creation of many temporary blobs when using the concatenation operator.