BIN Function in SQL

The BIN is a SQL function which converts the given decimal number to its binary equivalent. This function return NULL, if the NULL is passed in the function.

Syntax of BIN Function

In the BIN syntax, we have to pass that decimal number whose binary equivalent we want to find.

In the Structured Query Language, we can also use the BIN function with the column of the table as shown in the following block:

In this syntax, we have to define the name and column of that table on which we want to perform the BIN function.

Examples of BIN function

Example 1: This example returns the binary representation of the specified number:

Output:

Binary_of_101
1100101

Example 2: This example returns the binary representation of the specified number:

Output:

Binary_of_2
10

Example 3: This example returns the binary representation of 8:

Output:

Binary_of_8
1000

Example 4: This example returns the binary representation of 255:

Output:

Binary_of_255
11111111

Example 5: This example returns the binary representation of NULL:

SELECT BIN(NULL) AS Binary_of_NULL;

Output:

Binary_of_NUL
NULL

Example 6: This example returns the binary representation of NULL:

SELECT BIN(21) AS 21, BIN(22) AS 22, BIN(23) AS 23, BIN(24) AS 24, BIN(25) AS 25, BIN(26) AS 26, BIN(27) AS 27, BIN(28) AS 28, BIN(29) AS 29, BIN(30) AS 30;

Output:

21	22	23	24	25	26	27	28	29	30
10101	10110	10111	11000	11001	11010	11011	11100	11101	11110

Example 7: This example uses the BIN function with the SQL table.

In this example, we will create the new table through which we will perform the BIN function on the columns of the table:

The following shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
.......,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

CREATE TABLE Product_Details
(
Product_ID INT NOT NULL,
Product_Name Varchar(50),
Product_Quantity INT,
Purchasing_Price INT,
Selling_Price INT,
Release_Date Date, 
Product_Rating INT
);

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

Product_ID	Product_Name	Product_Quantity	Purchasing_Price	Selling_Price	Release_Date	Product_Rating
104	P1	10.250	945	NULL	2022-04-30	NULL
202	P4	15.500	45	75	2022-01-28	5
103	P2	18.250	25	NULL	2022-02-18	4
111	P7	25.250	5	15	2021-12-25	9
210	P6	15.500	50	70	2021-10-15	NULL
212	P8	19.750	110	250	2022-01-28	4
112	P10	10.250	550	835	2022-04-11	NULL

Query 1: The following SELECT query uses the BIN function with the Product_Quantity column of the above Product_Details table:

SELECT Product_ID, BIN(Product_ID) AS Binary_of_Product_ID FROM Product_Details;

This query shows the binary representation of product id of each product.

Output:

Product_ID	Binary_of_Product_ID
104	1101000
202	11001010
103	1100111
111	1101111
210	11010010
212	11010100
112	1110000

Query 2: The following SELECT query uses the BIN function with the and Purchasing_Price and Selling_Price column of the above Product_Details table:

SELECT Purchasing_Price, BIN(Purchasing_Price) AS Binary_of_PurchasingPrice, Selling_Price, BIN(Selling_Price) AS Binary_of_SellingPrice FROM Product_Details;

This query shows the binary representation of purchasing and selling price of each product.

Output: