BIN() Function in SQL

BIN() function in SQL is used when working with binary numbers, networking concepts, bitwise operations, access control systems and low-level computer programming. It helps convert integers into binary strings that represent how computers internally store numeric values.

What is the BIN() Function in SQL?

The BIN() function in SQL converts an integer into its corresponding binary form. It returns a string that has binary digits i.e. 0 and 1.

Syntax

Parameters

number: It is an integer value that needs to be converted to binary form.

Return Value

The BIN() function returns the binary equivalent of the input decimal number as a string.

• If the input number is positive then the function returns the binary equivalent of the input number.
• If the input number is zero then the function returns 0.

Creating a Table

Let us create a table named ComputerDevices that will store information about different networking and computing devices commonly used in organizations.

The table consists of the following fields:

DeviceID: It is a unique number assigned to each device.

DeviceName: It will store the name of the device.

IPAddressLastOctet: It will store the last octet of an IP address which is often used in networking calculations.

AccessLevel: It will represent the access permission level assigned to the device.

PortNumber: It will store the network port number used by the device for communication.

SQL Query

Inserting Records into the Table

Now we will insert records into the ComputerDevices table.

SQL Query

Retrieving the Records

Let us first retrieve the records from the table before applying the BIN() function.

SQL Query:

ComputerDevices Table:

Examples of BIN() Function in SQL

The table above contains device-related details for various computer devices. We will apply the BIN() function to different numeric columns to transform their decimal values into binary representations.

Example 1: Converting an Integer into Binary

In this example, we will explore how to utilize the BIN() function to convert an integer into its binary equivalent.

SQL Query:

Output:

Explanation:

We have here utilized the BIN() function to convert the integer 25 into its binary form. The function returned the binary string 11001 corresponds to the integer 25 in binary form.

Example 2: Converting the Result of an Arithmetic Expression into Binary

Here we will apply the BIN() function to the result of an arithmetic expression. It demonstrates that BIN() can transform calculated values as well as direct numbers.

SQL Query:

Output:

Explanation:

The expression 50 + 14 is evaluated first which yields the value 64. The BIN() function then converts the number 64 into its binary form which is 1000000.

Example 3: Converting Zero into Binary

We will be utilizing the BIN() function to convert the decimal value 0 into its binary representation.

SQL Query:

Output:

Explanation:

In the above query, we have used the BIN() function to convert the value 0 into binary format. Since the binary representation of zero is also zero so the function returns 0.

Example 4: Using BIN() Function with NULL

We will here be discussing how the BIN() function deals with NULL value is passed as an argument.

SQL Query:

Output:

Explanation:

We have here utilized BIN() function to convert a NULL value into binary format. It simply returns NULL.

Example 5: Converting a Negative Value into Binary

In this example we have utilized the BIN() function to convert a negative value into its binary representation.

SQL Query:

Output:

Explanation:

We have here used the BIN() function to convert the negative number -5 into binary format. The function internally uses the storage of data type to convert the value into binary. For this reason, we get a long binary string as the output.

Example 6: Converting Device IDs into Binary

We will utilize the BIN() function on the DeviceID column. Device ID is a stored as decimal integers but the BIN() function can convert them into binary values to show how computers internally represent numeric identifiers.

SQL Query:

Output:

Explanation:

We have used the BIN() function in the above example to convert each DeviceID into its binary value. As a result, we get the binary representation of the DeviceID.

Example 7: Converting IP Address Values into Binary

In this example below, we will be using BIN() function on the IPAddressLastOctet column. It can be particularly useful in networking and IP address calculation tasks.

SQL Query:

Output:

Explanation:

We have applied BIN() function on the IPAddressLastOctet column. This column stores the last Octet of the IPv4 addresses. We can use BIN() function to convert these decimal values into their binary equivalent.

Example 8: Converting Access Levels into Binary

In this example we will convert the AccessLevel column into binary format with the help of BIN() function. Access levels are commonly used in security systems and permission management. Binary values are often used internally to represent permission flags and access controls.

SQL Query:

Output:

Explanation:

The BIN() function in this demonstration converts the AccessLevel values into binary form. It is useful in understanding how access permissions are internally stored.

Example 9: Converting IP Address Values into Binary Using the WHERE Condition

Let us combine the BIN() function with the WHERE clause together. The WHERE clause will first filter the relevant records and BIN() will convert the filtered decimal values into binary.

SQL Query:

Output:

Explanation:

In this example, we have combined BIN() function with WHERE clause to first filter the IPAddressLastOctet values that are larger than 50. After filtering the relevant decimal numbers, we use the BIN() function to convert these values into their binary equivalent.

Example 10: Display Binary Port Numbers in Ascending Order

Here we will be using BIN() function on the PortNumber column and then sort the results according to the original decimal values of ports.

SQL Query:

Output:

Explanation:

We have utilized BIN() function on the PortNumber column to convert the port numbers into binary. The resulting binary numbers can be used to determine the binary representation of the port numbers in networking. We have also utilized the ORDER BY clause to sort the results according to the original decimal values stored in the PortNumber column.

Rules and Behavior of SQL BIN() Function

• The input value should be a valid integer or a value that can be converted to an integer.
• BIN(0) will return 0.
• Positive integers are converted to their binary equivalent.
• The function returns the result as a string containing only 0 and 1.
• If the input value is NULL then the function returns NULL.
• Fractional values are converted to integers before the binary conversion is performed.
• The output does not include prefixes such as 0b; only the binary digits are returned.
• Larger decimal values will generate larger binary strings.
• The BIN() function can be used with constants, table columns, arithmetic expressions, and SQL clauses such as SELECT, WHERE and ORDER BY.
• The function is commonly used in binary conversions, networking, bitwise operations and computer science applications.
• BIN() is supported in MySQL and returns the binary representation of a decimal number.
• The original decimal value remains unchanged and only the displayed output is converted into binary format.