SQL Reference - VARBINARY

VARBINARY

VARBINARY is a binary string data type in SQL, used to store binary data. This can include files, images, or any other type of data. The storage size varies based on the length specified during the declaration of a VARBINARY variable. Unlike the BINARY data type, VARBINARY values are variable in length.

Example

CREATE TABLE VarbinaryTest (
    id INT AUTO_INCREMENT,
    data VARBINARY(10),
    PRIMARY KEY (id)
);

INSERT INTO VarbinaryTest (data)
VALUES (0x5468697320495320412054657374);

Output

SELECT * FROM VarbinaryTest;

id	data
1	0x5468697320495320412054657374

Explanation

In this example, a VARBINARY column data is created and then populated with a hex value. When this value is later retrieved, it is shown in its binary form. The VARBINARY type stores binary data as a variable-length string, up to a maximum length that is specified in parentheses. It is useful for holding non-character data such as images or other types of binary files.

Example

CREATE TABLE VarbinaryExample
(
    ID INT PRIMARY KEY,
    BinaryData VARBINARY(MAX)
)
INSERT INTO VarbinaryExample (ID, BinaryData) VALUES (1, 0x74657374)
SELECT * FROM VarbinaryExample

Output

ID   BinaryData
-------------------
1    0x74657374

Explanation

The table VarbinaryExample is created with columns ID and BinaryData. The VARBINARY(MAX) datatype is used for the BinaryData column, which allows for the maximum storage size, up to 2^31-1 bytes. A value (1, 0x74657374) is then inserted into the table. The binary representation ‘0x74657374’ is hexadecimal for ‘test’. The SELECT statement retrieves all records from the table.

Example

DECLARE
  raw_data VARBINARY(100);
BEGIN
  raw_data := UTL_RAW.CAST_TO_RAW('Hello World');
  DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(raw_data));
END;

Output

Hello World

Explanation

The VARBINARY data type in Oracle is used to store binary data as variable-length arrays. The code above declares a VARBINARY variable raw_data and assigns it the binary representation of the string ‘Hello World’. The UTL_RAW.CAST_TO_RAW function is used to convert ‘Hello World’ to its binary representation, and UTL_RAW.CAST_TO_VARCHAR2 is used to convert the binary data back into a string for display. The output of the code is the original string ‘Hello World’.