SQL Tutorial SQL Advanced SQL Database SQL References

MySQL BINARY() Function



The MySQL BINARY() function converts a value to a binary string. This function is equivalent to using CAST(value AS BINARY).

Syntax

/* version 1 */
BINARY(value)

/* version 2 */
BINARY value

Parameters

value Required. Specify the value to convert to a binary string.

Return Value

Returns the converted value as a binary string.

Example:

The example below shows the usage of BINARY() function.

mysql> SELECT BINARY(123);
Result: '123'

mysql> SELECT BINARY('alphacodingskills.com');
Result: 'alphacodingskills.com'

mysql> SELECT BINARY('A');
Result: 'A'

mysql> SELECT BINARY 123;
Result: '123'

mysql> SELECT BINARY 'alphacodingskills.com';
Result: 'alphacodingskills.com'

mysql> SELECT BINARY 'A';
Result: 'A'

Using BINARY() function to compare strings byte-by-byte

When = operator is used, MySQL performs a character-by-character comparison of strings.

Example:

In the example below, MySQL performs a character-by-character comparison of 'HELLO' and 'hello' and return 1 (because on a character-by-character basis, 'HELLO' and 'hello' are equivalent):

mysql> SELECT 'HELLO' = 'hello';
Result: 1

To perform a byte-by-byte comparison of strings, the BINARY() function can be used which cast a value to a binary string and forces a byte-by-byte comparison of strings.

Example:

In the example below, MySQL performs a byte-by-byte comparison of 'HELLO' and 'hello' and return 0 (because on a byte-by-byte basis, 'HELLO' and 'hello' are NOT equivalent):

mysql> SELECT BINARY 'HELLO' = 'hello';
Result: 0

❮ MySQL Functions