EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL BIN()
 

MySQL BIN()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

MySQL BIN()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL BIN() output 1

  • 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:

MySQL BIN() output 2

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:

MySQL BIN() output 3

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:

MySQL BIN() output 4

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

SELECT BIN(NULL);

Output:

MySQL BIN() output 5

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:

output 6

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:

output 7

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:

output 8

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

SELECT EmpName, EmpSalary, EmpPF FROM Employees;

Output:

output 9

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:

output 10

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW