BLOB data type

Table of Contents

Text BLOB support in functions and operators
Various enhancements

Text BLOB support in functions and operators

Changed in: 2.1, 2.1.5, 2.5.1

Description: Text BLOBs of any length and character set (including multi-byte sets) are now supported by practically every internal text function and operator. In a few cases there are limitations or bugs.

Level of support:

Full support for:
- = (assignment);
- =, <>, <, <=, >, >= and synonyms (comparison);
- || (concatenation);
- BETWEEN, IS [NOT] DISTINCT FROM, IN, ANY|SOME and ALL.
Support for STARTING [WITH], LIKE and CONTAINING:
- In versions 2.1–2.1.4 and 2.5, an error is raised if the second operand is 32 KB or longer, or if the first operand is a BLOB with character set NONE and the second operand is a BLOB of any length and character set.
- In versions 2.5.1 and up (as well as 2.1.5 and up in the 2.1 branch), each operand can be a BLOB of any length and character set.
SELECT DISTINCT, ORDER BY and GROUP BY work on the BLOB ID, not the contents. This makes them as good as useless, except that SELECT DISTINCT weeds out multiple NULLs, if present. GROUP BY behaves oddly in that it groups together equal rows if they are adjacent, but not if they are apart.
Any issues with BLOBs in internal functions and aggregate functions are discussed in their respective sections.

Various enhancements

Changed in: 2.0

Description: In Firebird 2.0, several enhancements have been implemented for text BLOBs:

DML COLLATE clauses are now supported.
Equality comparisons can be performed on the full BLOB contents.
Character set conversions are possible when assigning a BLOB to a BLOB or a string to a BLOB.

When defining binary BLOBs, the mnemonic binary can now be used instead of the integer 0.

Examples:

select NameBlob from MyTable
  where NameBlob collate pt_br = 'João'

create table MyPictures (
  id int not null primary key,
  title varchar(40),
  description varchar(200),
  picture blob sub_type binary
)