FROM_BASE64

Description

The FROM_BASE64 function decodes a Base64-encoded string back to its original string. This is the inverse operation of TO_BASE64 and follows RFC 4648 standards. Base64 encoding is commonly used to transmit binary data in text protocols, and this function can restore the encoded data. It supports standard Base64 character set (A-Z, a-z, 0-9, +, /) and padding character (=).

Syntax

FROM_BASE64(<str>)

Parameters

Parameter	Description
<str>	The Base64-encoded string to decode. Type: VARCHAR

Return Value

Returns VARCHAR type, representing the original string after Base64 decoding.

Decoding rules:

• Accepts standard Base64 character set: A-Z, a-z, 0-9, +, /
• Supports padding character =
• Follows RFC 4648 standards
• The input length must be a multiple of 4 (except for the empty string)
• Decoding result may contain non-printable characters

Special cases:

• If input is NULL, returns NULL
• If input contains illegal Base64 characters (including whitespace such as spaces and newlines), returns NULL
• If input is an empty string, returns an empty string
• If the input length is not a multiple of 4, returns NULL
• If padding is incorrect or format is invalid, returns NULL

Version note (4.0.8)

Version 4.0.8 corrects the decode buffer sizing of FROM_BASE64 and defines how invalid input is handled: an input whose length is not a multiple of 4 returns NULL directly. Previously the buffer was sized at half the input length, which was too small, so invalid input could trigger an out-of-bounds write. The encode buffer sizing of TO_BASE64 was corrected in the same version.

Examples

1. Basic decoding

SELECT FROM_BASE64('MQ=='), FROM_BASE64('QQ==');

+---------------------+---------------------+
| FROM_BASE64('MQ==') | FROM_BASE64('QQ==') |
+---------------------+---------------------+
| 1                   | A                   |
+---------------------+---------------------+

2. Multi-character decoding

SELECT FROM_BASE64('MjM0'), FROM_BASE64('SGVsbG8=');

+---------------------+-------------------------+
| FROM_BASE64('MjM0') | FROM_BASE64('SGVsbG8=') |
+---------------------+-------------------------+
| 234                 | Hello                   |
+---------------------+-------------------------+

3. NULL value handling

SELECT FROM_BASE64(NULL);

+-------------------+
| FROM_BASE64(NULL) |
+-------------------+
| NULL              |
+-------------------+

4. Empty string handling

SELECT FROM_BASE64('');

+-----------------+
| FROM_BASE64('') |
+-----------------+
|                 |
+-----------------+

5. Illegal character handling

SELECT FROM_BASE64('!!!'), FROM_BASE64('ABC@DEF');

+--------------------+------------------------+
| FROM_BASE64('!!!') | FROM_BASE64('ABC@DEF') |
+--------------------+------------------------+
| NULL               | NULL                   |
+--------------------+------------------------+

6. Longer payloads

SELECT FROM_BASE64('SGVsbG8gV29ybGQ='), FROM_BASE64('VGhlIHF1aWNrIGJyb3duIGZveA==');

+---------------------------------+---------------------------------------------+
| FROM_BASE64('SGVsbG8gV29ybGQ=') | FROM_BASE64('VGhlIHF1aWNrIGJyb3duIGZveA==') |
+---------------------------------+---------------------------------------------+
| Hello World                     | The quick brown fox                         |
+---------------------------------+---------------------------------------------+

7. UTF-8 multi-byte payloads

SELECT FROM_BASE64('4bmt4bmbw6w='), FROM_BASE64('4biN4biNdW1haSBoZWxsbw==');

+-----------------------------+-----------------------------------------+
| FROM_BASE64('4bmt4bmbw6w=') | FROM_BASE64('4biN4biNdW1haSBoZWxsbw==') |
+-----------------------------+-----------------------------------------+
| ṭṛì                         | ḍḍumai hello                            |
+-----------------------------+-----------------------------------------+

8. Email addresses

SELECT FROM_BASE64('dXNlckBleGFtcGxlLmNvbQ=='), FROM_BASE64('YWRtaW4udGVzdEBjb21wYW55Lm9yZw==');

+-----------------------------------------+-------------------------------------------------+
| FROM_BASE64('dXNlckBleGFtcGxlLmNvbQ==') | FROM_BASE64('YWRtaW4udGVzdEBjb21wYW55Lm9yZw==') |
+-----------------------------------------+-------------------------------------------------+
| user@example.com                        | admin.test@company.org                          |
+-----------------------------------------+-------------------------------------------------+

9. JSON payloads

SELECT FROM_BASE64('eyJuYW1lIjoiSm9obiIsImFnZSI6MzB9'), FROM_BASE64('WzEsMiwzLDQsNV0=');

+-------------------------------------------------+---------------------------------+
| FROM_BASE64('eyJuYW1lIjoiSm9obiIsImFnZSI6MzB9') | FROM_BASE64('WzEsMiwzLDQsNV0=') |
+-------------------------------------------------+---------------------------------+
| {"name":"John","age":30}                        | [1,2,3,4,5]                     |
+-------------------------------------------------+---------------------------------+

10. Round-trip with TO_BASE64

SELECT FROM_BASE64(TO_BASE64('Hello')), FROM_BASE64(TO_BASE64('测试'));

+---------------------------------+----------------------------------+
| FROM_BASE64(TO_BASE64('Hello')) | FROM_BASE64(TO_BASE64('测试'))   |
+---------------------------------+----------------------------------+
| Hello                           | 测试                             |
+---------------------------------+----------------------------------+