Binary data types

SQL Guide : Data types : Binary data types

Binary data types

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

* default is 1 ** default is 254

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

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

Similarly, you may 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);

WARNING: this kind of query cannot use indexed search for the LIKE predicate and results in poor query performance in many cases.