MySQL BIN()

Introduction to MySQL BIN()

MySQL BIN() function represents one of a string function of converting a given number into the binary symbol as a string value result. This function is responsible for binary representation of digits in the form of series value. The MySQL BIN() function returns an identical binary string illustration of the provided binary value of a BIGINT numeral. While execution, if the argument’s value in the function is, provides as NULL then, the resultant value of MySQL BIN() function will also be NULL.

The correspondent of BIN() function is considered as CONV() function whose syntax structure format is CONV(Num,10,2). Here, the term Num denotes the BIGINT number, 10 represents base 10 decimal, and binary with base 2. In the format of the CONV() function, the number is changed from base 10 decimal number to base 2 binary digit string when executed. It means this returns a similar output as BIN() function gives.

Syntax

The succeeding structure defines the syntax of BIN() function in MySQL:

SELECT BIN(Dec_Num);

The parameter used in the above syntax of BIN() MySQL function is defined as the decimal digit or digits which is used for converting into its corresponding binary sequence value when we apply the function on a query in the database table.

This value returned is in a string from representing base 2 value that is equal to the decimal number passed as a parameter with the BIN() function in MySQL.

For using this function while implementing in an SQL query, we need to follow the following syntax:

SELECT BIN(Num_Expr) FROM Table_Source;

Here, the Num_Expr denotes the BIGINT number that accepts valid specified expression, column value, or any other numeral which is converted to a binary string when the result is fetched from the database query. Table_Source is the name of the database table.

How does BIN() function work in MySQL?

As mentioned above, the BIN() function works to produce a binary representation of the number declared in the function argument.

Suppose, we want to convert a number 12 into a binary string output then, using the syntax we have basic query statement as below:

SELECT BIN(12);

Output:

• The output shows a binary representation of given number 12 which is a decimal and gets converted to a base 2.
• It should be distinguished that the string value basedBIN() function gives NULL as a result when the conversion of the number into binary string value has the length that exceeds the value of the max_allowed_packetsystem variable.
• In this process, the initial position of the string but for those MySQL functions which work on the string is numbered as 1.
• For those functions which implement length arguments, the non-integer parameters are rounded to the close integer.

For example,

SELECT BIN(5126);

Output:

Now, we will take about CONV() function for the conversion then, the result of the function will be as follows:

SELECT CONV(5,10,2);

Output:

Here, the conversion takes place and the number 5 value is changed from decimal to base 2 i.e. binary string as shown in the above screenshot.

Examples of MySQL BIN()

We are now going to demonstrate the MySQL BIN() function and its some uses as per the following examples:

Example #1

MySQL BIN() function simple examples:

SELECT BIN(34.56);

Output:

We are executing the BIN() function with a NULL value to pass in the argument.

SELECT BIN(NULL);

Output:

Also, let us give any character value with Varchar data type in MySQL, then the conversion result will be as below:

SELECT BIN('ABC');

Output:

From the result, we come to know that if we pass any string value for the BIN() function, then the output is zero(0). Therefore, we must provide a numeral for retrieving the Binary value.

Example #2

MySQL BIN() function example with a certain range of values:

Let us consider the next example that demonstrates to produce several binary values from different numbers that range from 11 to 20 decimal digits. The statement for BIN() function is:

SELECT BIN(11) AS '11', BIN(12) AS '12', BIN(13) AS '13', BIN(14) AS '14', BIN(15) AS '15', BIN(16) AS '16', BIN(17) AS '17', BIN(18) AS '18', BIN(19) AS '19', BIN(20) AS '20';

Output:

Example #3

MySQL BIN() function example with Column values in the records of a table:

To implement this example, we have used the table data of a database table as a sample provided in the following example:

We have an Employee table, let’s create this one:

CREATE TABLE Employees
( EmpID int NOT NULL,
EmpName varchar(255) NOT NULL,
EmpProfile varchar(255),
EmpSalary int,
EmpPF int ,
PRIMARY KEY (EmpID) );

Let us insert some records with the following query statement:

INSERT INTO Employees (EmpID, EmpName, EmpProfile, EmpSalary, EmpPF)VALUES
('210', 'Radha', 'Engineer', '50000', '3600'),
('211', 'Mohan', 'Manager', '40000', '2000'),
('212', 'Dev', 'Executive', '32000', '1800'),
('213', 'Madhuri', 'Blogger', '20000', Null),
('214', 'Rita', 'Pilot', '48000', '5000');

The table is displayed as:

SELECT * FROM Employees;

Output:

First of all, let us view the information through the below MySQL statement:

SELECT EmpName, EmpSalary, EmpPF FROM Employees;

Output:

Now, we apply MySQL BIN() function on the selected columns in the table above. We will write the below statement to provide the binary values of the column data values available in integer data type columns i.e. EmpSalary and EmpPF.

The BIN() function along with the SELECT query is implemented here to find the binary string value that is equivalent to the present respective column values in the given table:

SELECT EmpName, EmpProfile, EmpSalary,BIN(EmpSalary) AS Binary_Salary,EmpPF, BIN(EmpPF) AS Binary_PF FROM Employees;

Output:

We have shown the column data binary representation of the corresponding table column values that we have applied as input to the MySQL BIN() function arguments.

This MySQL BIN() function helps to evaluate the binary form of any number as we implement to convert the decimal number into a binary one, whenwe starts calculating manually using the mathematical formula, isn’t it?

Conclusion

• The MySQL BIN() function converts a decimal numeral to its comparable binary string value when the argument is passed using the function in a MySQL Statement execution.
• BIN() function holds a binary result of Dec_Num as in the syntax described above indicating a long long number i.e. BIGINT.
• BIN() function is for number conversion to base 2 value but the CONV() function can enable us to generate results on different bases, not only restricted to a binary one.

Recommended Articles

This is a guide to MySQL BIN(). Here we discuss how does BIN() function works in MySQL with respective examples for better understanding. You may also look at the following articles to learn more –

1. MySQL Subquery
2. PostgreSQL Interval
3. ANY in SQL
4. NOT in MySQL