How the BIN() function works in Mariadb?

The BIN() function in MariaDB is a simple yet powerful function used to convert a decimal number into its binary equivalent. This function is particularly useful when you need to perform bitwise operations or when working with data that is more naturally expressed in binary form.

Syntax

The syntax for the MariaDB BIN() function is as follows:

BIN(number)

The number parameter is the decimal value you want to convert to binary. The function returns the binary representation of the number as a string.

Examples

Example 1: Basic Usage of `BIN()`

This example shows how to convert the decimal number 12 into binary.

SELECT BIN(12);

+---------+
| BIN(12) |
+---------+
| 1100    |
+---------+

The output is the binary representation of the decimal number 12.

Example 2: Binary of a Negative Number

Here we convert a negative decimal number into binary.

SELECT BIN(-12);

+------------------------------------------------------------------+
| BIN(-12)                                                         |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111110100 |
+------------------------------------------------------------------+

The output is the binary representation of the decimal number -12, displayed in two’s complement form.

Example 3: Binary of Zero

Converting the decimal number 0 into binary.

SELECT BIN(0);

+--------+
| BIN(0) |
+--------+
| 0      |
+--------+

The output is the binary representation of the decimal number 0.

Example 4: Large Number Conversion

Converting a large decimal number into binary.

SELECT BIN(123456789);

+-----------------------------+
| BIN(123456789)              |
+-----------------------------+
| 111010110111100110100010101 |
+-----------------------------+

The output is the binary representation of the decimal number 123456789.

Example 5: Using `BIN()` with a Table

First, create a table with sample data:

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (value INT);
INSERT INTO numbers VALUES (12), (0), (-12), (123456789);

Now, convert each number to binary:

SELECT value, BIN(value) AS binary_representation FROM numbers;

+-----------+------------------------------------------------------------------+
| value     | binary_representation                                            |
+-----------+------------------------------------------------------------------+
|        12 | 1100                                                             |
|         0 | 0                                                                |
|       -12 | 1111111111111111111111111111111111111111111111111111111111110100 |
| 123456789 | 111010110111100110100010101                                      |
+-----------+------------------------------------------------------------------+

The output shows the binary representation for each number in the numbers table.

Below are a few functions related to the MariaDB BIN() function:

MariaDB CONV() function is used to convert numbers between different number bases.
MariaDB HEX() function converts a number into its hexadecimal equivalent.
MariaDB OCT() function converts a number into its octal equivalent.

Conclusion

The BIN() function is an essential tool for developers and database administrators who need to work with binary data in MariaDB. It provides a straightforward way to convert decimal numbers to binary, facilitating operations that require binary manipulation. Understanding how to use this function, along with its related functions, can greatly enhance your ability to work with different number bases and perform complex calculations within your database applications.