Updated May 12, 2023
Introduction to MySQL BIN()
MySQL BIN() function represents one of the string functions of converting a given number into the binary symbol as a string value result. This function is responsible for the binary representation of digits in a 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, the resultant value of MySQL BIN() function will also be NULL.
The CONV() function in MySQL has a syntax structure format of CONV(Num,10,2) and is considered the equivalent of the BIN() function. Here, Num denotes the BIGINT number, 10 represents base 10 decimal, and binary with base 2. When executed in the CONV() function format, the number changes from a base 10 decimal number to a base two binary digit string. It means this returns a similar output as BIN() function gives.
Syntax
The succeeding structure defines the syntax of the BIN() function in MySQL:
SELECT BIN(Dec_Num);The parameter used in the above syntax of the BIN() MySQL function is the decimal digit or digits. It converts 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 representing a base 2 value equal to the decimal number passed as a parameter with MySQL’s BIN() function.
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 a valid specified expression, column value, or any other numeral 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 produces a binary representation of the number declared in the function argument.
Suppose we want to convert the number 12 into a binary string output. Then, using the syntax, we have an introductory query statement as below:
SELECT BIN(12);Output:
- The output displays the binary representation of the decimal number 12, which is obtained by converting it to base 2.
- It should be distinguished that the string value basedBIN() function gives NULL when the conversion of the number into binary string value has a 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 that work on the string is numbered 1.
- For those functions which implement length arguments, the non-integer parameters are rounded to the nearest 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:
The process of base-2 conversion converts the decimal number 5 to its equivalent binary string representation, as shown in the screenshot above.
Examples of MySQL BIN()
We are now going to demonstrate the MySQL BIN() function and its 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 the Varchar data type in MySQL, then the conversion result will be as below:
SELECT BIN('ABC');Output:
From the result, we know that if we pass any string value for the BIN() function, 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 produces several binary values from different numbers ranging 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 follows:
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 to 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.
Here, we use the BIN() function along with the SELECT query to find the binary string value equivalent to the current corresponding 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 evaluate the binary form of any number as we convert the decimal number into a binary one when we start calculating manually using the mathematical formula.
Conclusion
- The MySQL BIN() function converts a decimal numeral to its equivalent binary string value when we pass the argument 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
We hope that this EDUCBA information on “MySQL BIN()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
