Binary String Functions

Binary string functions

These apply to MonetDB SQL binary string type: blob.

To use binary string data in scripts you can use the hex cast notation: x'0012FF' to represent 3 bytes: 0, 18 (16+2) and 255.

The blob cast notation: blob'0012FF' is also allowed.

Examples

In MonetDB binary string data is stored as hex character strings, using two hex characters per byte. Therefore some of the character string functions can also be applied to binary string data, for example:

SELECT x'1a2b3c4d5e';
SELECT blob '1a2b3c4d5e';

CREATE TABLE test_blob(b blob NOT NULL, b52 blob(52));
INSERT INTO test_blob(b,b52) VALUES (x'123456', blob'78901234');
INSERT INTO test_blob(b,b52) VALUES ('123456abcdef', '78901234abcdef'); -- implicit conversion from char to blob is done

SELECT length(b) as byte_length, octet_length(b) as octet_length, b from test_blob where b = x'123456';
-- returns: 3 3 123456
SELECT length(b) as byte_length, char_length(b) as char_length, b from test_blob where b = x'123456';
-- returns: 3 6 123456

-- example of how to extract part of a binary string
SELECT b52, substring(b52, 3, 4) as part_as_Clob from test_blob;
SELECT b52, cast(substring(b52, 3, 4) as blob) as part_as_Blob from test_blob;

-- example of how to concatenate two binary strings
SELECT b||b52 as b_concat_result_Clob from test_blob;
SELECT convert(b||b52 , blob) as b_concat_result_Blob from test_blob;

-- comparisons are allowed, except for LIKE and ILIKE pattern matches on blobs
SELECT * from test_blob where b < b52;
SELECT * from test_blob where b like '1234%'; -- NOT allowed, generates an ERROR
SELECT * from test_blob where substring(b,0,9) like '1234%'; -- This is allowed as substring() returns a Clob