MySQL BIN() function

BIN() function

MySQL BIN() returns the corresponding string representation of the binary value of a BIGINT number. It returns NULL if the number is NULL. The equivalent of BIN() is CONV(N,10,2), where N is a BIGINT number. The binary representation produced by the BIN() function is a string of 0s and 1s, not a numeric value.

This function is useful in -

• Decimal to binary conversion: It allows us to convert a decimal number to its binary representation.
• Formatting and display: You can use the BIN() function to display or format decimal numbers as binary strings.

Syntax:

BIN(num1)

Argument:

Syntax Diagram:

MySQL Version: 8.0

Example: MySQL BIN() function

The following MySQL statement returns the string representation of 255, i.e. '11111111'.

Code:

SELECT BIN(255);

Output:

mysql> SELECT BIN(255);
+----------+
| BIN(255) |
+----------+
| 11111111 | 
+----------+
1 row in set (0.01 sec)

Convert a column value to binary

The following MySQL statement converts the values of srno from the numtable to binary by using the BIN() function.

Code:

SELECT srno, BIN(srno) AS binary_value
FROM numtable;

Sample table: numtable

Output:

srno|binary_value|
----+------------+
   1|1           |
   2|10          |
   3|11          |
   4|100         |
   5|101         |
   6|110         |
   7|111         |
   8|1000        |
   9|1001        |
  10|1010        |

Use the BIN() function with arithmetic operations

The following MySQL statement combine the BIN() function with other arithmetic operations to manipulate binary values.

Code:

SELECT BIN(9 + 6);

Output:

BIN(9 + 6)|
----------+
1111      |

Video Presentation:

All String Functions (Slides presentation)

Previous: ASCII
Next: BIT_LENGTH