Binary data types

Binary data types

The following table describes the binary data types. See SQL: Data types for a description of the column headings.

(* default is 1, ** default is 254)

Data type	Size	Precision	Scale	Length	Display Size
BINARY	2G*	DEFLEN	N/A	DEFLEN	DEFLEN x 2
VARBINARY	2G**	DEFLEN	N/A	DEFLEN	DEFLEN x 2
LONG VARBINARY BLOB	2G	MAXLEN	N/A	MAXLEN	MAXLEN x 2

To insert values into BINARY, VARBINARY, and LONG VARBINARY fields, you can express the value as hexadecimal and use the CAST operator, for example:

INSERT INTO table1 VALUES (CAST('FF00AA55' AS VARBINARY));

Similarly, you can use CAST() expressions in WHERE clauses:

CREATE TABLE t1 (x VARBINARY);

INSERT INTO t1 (x) VALUES (CAST('000000A512' AS VARBINARY));

INSERT INTO t1 (x) VALUES (CAST('000000FF12' AS VARBINARY));

-- To compare the VARBINARY value(s) using LIKE, cast the
-- VARBINARY to VARCHAR.

SELECT * FROM t1 WHERE CAST(x AS VARCHAR) LIKE '000000A5%'; SELECT * FROM t1 WHERE CAST(x AS VARCHAR) LIKE '000000A5__';

-- NOTE: If you want to use "=" rather than "LIKE" then you
-- can cast either operand.

SELECT * FROM t1 WHERE CAST(x AS VARCHAR) = '000000A512'; SELECT * FROM t1 WHERE x = CAST('000000A512' AS VARBINARY);

Note This kind of query cannot use indexed search for the LIKE predicate and results in poor query performance in many cases.

Go up to