SQL Reference - BINARY

BINARY(M)

m: This parameter defines the length of the binary string. The length can be a value from 0 to 255, and it determines the maximum number of bytes required for storing the binary string. If the input binary string is less than M bytes, it is padded with trailing zeros to match the specified length.

Example

SELECT BINARY 'A' = 'a';

Output

Explanation

This SQL command is comparing the binary values of ‘A’ and ‘a’. In binary comparison, case sensitivity matters, hence the output is ‘0’ (False), indicating that ‘A’ and ‘a’ are not considered equal because one is uppercase and the other is lowercase.

BINARY [ ( length ) ]

length: This defines the length of the binary string. It is an integer value specifying the number of bytes. The length can be a value from 1 through 8,000.

Example

SELECT BINARY 'SQL Server';

Output

SQL Server

Explanation

In this given example, BINARY is used to transform the string ‘SQL Server’ into a binary string. The result is the same ‘SQL Server’ text that was input, but it is now stored as binary data. The BINARY keyword is useful for equality comparisons, ordering of data, as well as storing large fixed-length binary values.

BINARY_FLOAT(value)BINARY_DOUBLE(value)

value: This argument is the input for the BINARY_FLOAT or BINARY_DOUBLE function in Oracle. It represents the number that will be converted to either the binary float or binary double data type. It could be a constant, variable, or column that contains numeric data.

Example

SELECT CAST('Hello' AS BINARY(10));

Output

48656C6C6F2020202020

Explanation

In the example provided, the string ‘Hello’ is converted into binary data utilizing the CAST function in SQL. The binary output corresponds with ‘Hello’ padded with spaces up to 10 places.

BINARY(X,Y)

x: Specifies the length of the binary string. This is the number of bytes that can be stored in this string. For SQLite, this should be a positive integer.
y: SQLite does not use this parameter. In other database management systems, this parameter might define the display width of the binary string.

Example

CREATE TABLE temp (data BINARY(5));
INSERT INTO temp (data) VALUES ('Hello');
SELECT * FROM temp;

Output

| data |
|------|
| Hello|

Explanation

In SQLite, the BINARY keyword is used to indicate that the stored value has binary data type. In the given example, the CREATE TABLE statement creates a table named temp with a single column data which is of type BINARY(5). It indicates that the data column can hold up to 5 bytes of binary data. The INSERT INTO statement is used to insert a string ‘Hello’ into the data column. The SELECT * FROM temp then retrieves all records from the temp table.