Convert Binary Data to Strings in MySQL

In MySQL, you can convert binary data to strings by using the CAST function, which allows you to specify the data type conversion from binary to character type.

In this tutorial, you will learn how to use the CAST function to convert binary data back into readable strings in MySQL.

Converting Binary Data to Strings in MySQL

In MySQL, binary data is stored in BINARY or VARBINARY data types and can be converted back to a string format using CAST or CONVERT. This is helpful for retrieving binary-stored text in a readable form.

Examples: Converting Binary Data to Strings in MySQL

Below are examples demonstrating how to use CAST and CONVERT to change binary data into string format in MySQL.

1. Using CAST to Convert Binary to String

To convert binary data BINARY 'Hello' to a readable string:

</>
Copy
SELECT CAST(BINARY 'Hello' AS CHAR) AS converted_string;

This query takes the binary representation of 'Hello' and converts it to a CHAR type, resulting in a readable string format.

Convert Binary Data to Strings in MySQL - Example: 1

2. Using CONVERT to Change Binary to String

Another way to convert binary data to a string format is by using the CONVERT function. To convert binary data in BINARY 'World' to a readable text:

</>
Copy
SELECT CONVERT(BINARY 'World' USING utf8) AS converted_text;

This query converts the binary form of 'World' into a readable UTF-8 string, making it displayable in its original text form.

Convert Binary Data to Strings in MySQL - Example: 2

3. Retrieving Binary Data as Text from a Table

To retrieve binary-stored text data from a table and display it as readable text, you can use CAST. For example, if you have a table documents with a binary column content:

</>
Copy
SELECT CAST(content AS CHAR) AS readable_content
FROM documents;

This query retrieves each row’s binary content and converts it to readable text, making it suitable for display.