MySQL FROM_BASE64() String Function
MySQL FROM_BASE64() String Function
The MySQL FROM_BASE64()
string function decodes a base-64 encoded string and returns the result as a binary string. This function is essential for decoding data that has been encoded in base-64 format in SQL queries.
Syntax
SELECT FROM_BASE64(base64_string) AS result
FROM table_name;
The FROM_BASE64()
function has the following components:
base64_string
: The base-64 encoded string to be decoded.result
: An alias for the resulting decoded binary string.table_name
: The name of the table from which to retrieve the data.
Example MySQL FROM_BASE64() String Function
Let's look at some examples of the MySQL FROM_BASE64()
string function:
Step 1: Using the Database
USE mydatabase;
This query sets the context to the database named mydatabase
.

Step 2: Creating a Table
Create a table to work with:
CREATE TABLE encoded_strings (
id INT AUTO_INCREMENT PRIMARY KEY,
base64_string TEXT NOT NULL
);
This query creates a table named encoded_strings
with columns for id
and base64_string
.

Step 3: Inserting Initial Rows
Insert some initial rows into the table:
INSERT INTO encoded_strings (base64_string)
VALUES ('SGVsbG8gd29ybGQ='),
('TXlTUUw='),
('V29ybGQ='),
('U3RyaW5nIEVuY29kaW5n'),
('RnJvbSBCYXNlNjQ=');
This query inserts five rows into the encoded_strings
table.

Step 4: Using FROM_BASE64() with WHERE Clause
Use the FROM_BASE64()
function to decode a base-64 encoded string:
SELECT base64_string, FROM_BASE64(base64_string) AS decoded_string
FROM encoded_strings;
This query retrieves the base64_string
column from the encoded_strings
table and returns the decoded binary string.

Step 5: Using FROM_BASE64() with Multiple Columns
Use the FROM_BASE64()
function with multiple columns:
SELECT id, base64_string, FROM_BASE64(base64_string) AS decoded_string
FROM encoded_strings;
This query retrieves the id
and base64_string
columns from the encoded_strings
table and returns the decoded binary string.

Step 6: Using FROM_BASE64() with Constants
Use the FROM_BASE64()
function with constants:
SELECT FROM_BASE64('U29tZSBFbmNvZGVkIFN0cmluZw==') AS decoded_constant;
This query decodes the constant base-64 encoded string 'U29tZSBFbmNvZGVkIFN0cmluZw==' and returns the decoded string.

Conclusion
The MySQL FROM_BASE64()
function is a powerful tool for decoding base-64 encoded strings in SQL queries. Understanding how to use the FROM_BASE64()
function is essential for effective data querying and analysis in MySQL.