MySQL BIN() String Function

MySQL BIN() String Function

The MySQL BIN() string function is used to return a binary representation of a number. This function is essential for converting numeric values to their binary equivalents in SQL queries.

Syntax

SELECT BIN(number) AS result
FROM table_name;

The BIN() function has the following components:

  • number: The number to be converted to binary.
  • result: An alias for the resulting binary representation.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL BIN() String Function

Let's look at some examples of the MySQL BIN() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT NOT NULL
);

This query creates a table named numbers with columns for id and value.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO numbers (value)
VALUES (5),
       (10),
       (15),
       (20),
       (25);

This query inserts five rows into the numbers table.

MySQL INSERT INTO TABLE

Step 4: Using BIN() with WHERE Clause

Use the BIN() function to retrieve the binary representation of a number:

SELECT value, BIN(value) AS binary_value
FROM numbers;

This query retrieves the value column from the numbers table and returns the binary representation of value.

MySQL BIN() WITH WHERE CLAUSE

Step 5: Using BIN() with Multiple Columns

Use the BIN() function with multiple columns:

SELECT id, value, BIN(value) AS binary_value
FROM numbers;

This query retrieves the id and value columns from the numbers table and returns the binary representation of value.

MySQL BIN() WITH MULTIPLE COLUMNS

Step 6: Using BIN() with Constants

Use the BIN() function with constants:

SELECT BIN(42) AS binary_42, BIN(100) AS binary_100;

This query retrieves the binary representation of the constant numbers 42 and 100.

MySQL BIN() WITH CONSTANTS

Conclusion

The MySQL BIN() function is a powerful tool for converting numeric values to their binary representation in SQL queries. Understanding how to use the BIN() function is essential for effective data querying and analysis in MySQL.