Base64 in SQL

MySQL and MariaDB: TO_BASE64 and FROM_BASE64

SELECT TO_BASE64('héllo wörld');
-- aMOpbGxvIHfDtnJsZA==   (connection charset utf8mb4)

SELECT CONVERT(FROM_BASE64('aMOpbGxvIHfDtnJsZA==') USING utf8mb4);
-- héllo wörld

TO_BASE64 encodes the bytes of the string in its character set. With utf8mb4 you get UTF-8; a latin1 column gives Latin-1 bytes, so TO_BASE64(CONVERT('héllo' USING latin1)) returns aOlsbG8= instead of aMOpbGxv. FROM_BASE64 returns a binary string; CONVERT(... USING utf8mb4) turns it back into text.

TO_BASE64 wraps lines at 76 characters, like MIME:

SELECT LENGTH(TO_BASE64(REPEAT('x', 60)));            -- 81 (80 chars + 1 newline)
SELECT REPLACE(TO_BASE64(REPEAT('x', 60)), '\n', ''); -- single line, 80 chars

Invalid input does not raise an error. FROM_BASE64 returns NULL:

URL-safe conversion with string functions:

SELECT TRIM(TRAILING '=' FROM REPLACE(REPLACE(TO_BASE64('<<???>>'), '+', '-'), '/', '_'));
-- PDw_Pz8-Pg

SELECT FROM_BASE64(CONCAT(
  REPLACE(REPLACE('PDw_Pz8-Pg', '-', '+'), '_', '/'),
  REPEAT('=', (4 - LENGTH('PDw_Pz8-Pg') % 4) % 4)));
-- <<???>>

For longer values, also wrap the encoder in REPLACE(..., '\n', ''). Both functions exist since MySQL 5.6 and in MariaDB.

PostgreSQL: encode and decode

PostgreSQL works on bytea. Convert text with convert_to and back with convert_from:

SELECT encode(convert_to('héllo wörld', 'UTF8'), 'base64');
-- aMOpbGxvIHfDtnJsZA==

SELECT convert_from(decode('aMOpbGxvIHfDtnJsZA==', 'base64'), 'UTF8');
-- héllo wörld

Like MySQL, encode(..., 'base64') wraps at 76 characters; psql shows the newline as a + at the end of the line. Strip it with replace(encode(x, 'base64'), E'\n', '').

Unlike MySQL, invalid input raises an error:

decode('aGk', 'base64')
ERROR:  invalid base64 end sequence
HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.

decode('PDw_Pz8-Pg==', 'base64')
ERROR:  invalid symbol "_" found while decoding base64 sequence

aGk=!! fails with invalid symbol "!" and a with invalid base64 end sequence. Whitespace, including newlines and spaces, is ignored, and aGl= is accepted.

PostgreSQL 17 and 18 have no base64url format (unrecognized encoding: "base64url"), so convert with translate:

SELECT translate(rtrim(encode('<<???>>'::bytea, 'base64'), '='), '+/', '-_');
-- PDw_Pz8-Pg

SELECT convert_from(decode(
  rpad(translate('PDw_Pz8-Pg', '-_', '+/'), (length('PDw_Pz8-Pg') + 3) / 4 * 4, '='),
  'base64'), 'UTF8');
-- <<???>>

To validate without aborting a transaction, wrap decode in a PL/pgSQL function with an EXCEPTION WHEN others block that returns NULL.

SQL Server: the XML base64Binary trick

Before SQL Server 2025 there is no Base64 function. The XML type converts between varbinary and xs:base64Binary, which is Base64:

-- encode
DECLARE @bin varbinary(max) = CAST('hi' AS varbinary(max));
SELECT CAST('' AS xml).value('xs:base64Binary(sql:variable("@bin"))', 'varchar(max)');
-- aGk=

-- decode
DECLARE @b64 varchar(max) = 'aGk=';
SELECT CAST('' AS xml).value('xs:base64Binary(sql:variable("@b64"))', 'varbinary(max)');
-- 0x6869

FOR XML ... BINARY BASE64 and FOR JSON also emit varbinary columns as Base64: SELECT CAST('hi' AS varbinary(max)) AS bin FOR JSON PATH returns [{"bin":"aGk="}]. The XML conversion does not wrap lines: 60 bytes came out as a single 80-character string.

The XML methods need QUOTED_IDENTIFIER ON. sqlcmd defaults to off, giving Msg 1934: SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Pass -I or run SET QUOTED_IDENTIFIER ON.

Text encoding in SQL Server

nvarchar is UTF-16LE, so CAST(N'héllo wörld' AS varbinary(max)) encodes to aADpAGwAbABvACAAdwD2AHIAbABkAA==, which other systems decode with gaps between letters. For UTF-8 (SQL Server 2019+), go through a UTF-8 collation first:

DECLARE @bin varbinary(max) = CAST(CAST(N'héllo wörld'
    COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS varchar(max)) AS varbinary(max));
-- encodes to aMOpbGxvIHfDtnJsZA==

Decoding UTF-8 back needs a UTF-8 target. A plain CAST(@raw AS varchar(max)) in a database with the default SQL_Latin1_General_CP1_CI_AS collation shows héllo wörld. Inserting the bytes into a column declared varchar(max) COLLATE Latin1_General_100_CI_AS_SC_UTF8 gives héllo wörld. For UTF-16 data, CAST(@raw AS nvarchar(max)) is enough.

Errors

With a string literal, bad Base64 fails at compile time: Msg 9319: XQuery [value()]: Static simple type validation: Invalid simple type value 'aGk'. The same input passed through sql:variable returns NULL instead. Missing padding, URL-safe characters (PDw_Pz8-Pg==) and non-zero padding bits (aGl=) were all rejected; a newline inside the string was accepted.

SQL Server 2025: BASE64_ENCODE and BASE64_DECODE

SQL Server 2025 (17.x) and Azure SQL add real functions, including a URL-safe flag:

SELECT BASE64_ENCODE(0x3C3C3F3F3F3E3E);      -- PDw/Pz8+Pg==
SELECT BASE64_ENCODE(0x3C3C3F3F3F3E3E, 1);   -- PDw_Pz8-Pg   (url_safe, no padding)
SELECT BASE64_DECODE('PDw_Pz8-Pg');          -- 0x3C3C3F3F3F3E3E

BASE64_ENCODE takes varbinary only; passing varchar fails with Msg 8116: Argument data type varchar(max) is invalid for argument 1 of base64_encode function. BASE64_DECODE accepts both alphabets, with or without padding, and aGl=. Invalid input such as aGk=!! or a raises Msg 9803: Invalid data for type "Base64Decode".

Pitfalls across databases

• Character set of the input. The same text gives different Base64 in a latin1 column, a utf8mb4 column and an nvarchar column. Decide on UTF-8 bytes and convert explicitly.
• Hidden newlines. MySQL and PostgreSQL wrap at 76 characters. JSON consumers and HTTP headers break on the embedded \n.
• Silent NULLs. MySQL and the SQL Server XML trick return NULL for bad input, which a JOIN or WHERE quietly drops.
• Large values. Encoding a BLOB in a query moves 33% more data over the wire. To check a stored value by hand, select the Base64 and open it with Base64 to file or Base64 to hex.

FAQ