Base64 Decoding in SQL: A Complete Guide

Open any production database long enough and you will meet the disguise. An avatar that arrived as a wall of letters inside a JSON export. A JWT token parked in a varchar column next to a user id. A certificate that somebody decided to ship as a string because the transfer format had no binary. Somewhere in a table, your data is wearing letters, and your job is to take them off without leaving the database.

In SQL, that job has one very comforting property: once you know which decoder your dialect speaks, the whole job shrinks to a single function call. The format itself is already explained in detail on the home page (64 printable characters, every group of four standing in for three input bytes, up to two = signs padding the final group), so this article skips the lecture. Two things to carry with you: base64 is a way of dressing bytes as text, not a lock, and decoding is the direction where the data gets smaller (back to three quarters of its encoded size), which is exactly the opposite of what your storage column was sized for. The real story is that SQL is a family of dialects, and every member calls its decoder by a different name and reacts to bad input with a completely different temperament. This article is the tour.

The Decoder Lineup

Here is who is on duty, and how each one behaves when the input is garbage. The last column matters, because a decoder that fails loudly in staging and fails silently in production is how missing avatars get into the field:

Notice the shape of the table: the function name is never the hard part. The hard part is the "when it breaks" column, because that column decides whether your report quietly loses rows or your batch job stops and asks for help.

MySQL and MariaDB: The Decoder That Shrugs

Both servers share the pair TO_BASE64() / FROM_BASE64(). The decoder takes a string and hands back a binary string: a sequence of bytes with no character set attached. A NULL in gives a NULL out, and here is the first thing to memorize: anything else that is not valid base64 is also a NULL, with no warning. The decoder shrugs, and your query happily keeps moving.

SELECT FROM_BASE64('aGVsbG8=') AS restored;
SELECT HEX(FROM_BASE64('aGVsbG8=')) AS as_hex;
SELECT CONVERT(FROM_BASE64('aGVsbG8gd29ybGQ=') USING utf8mb4) AS as_text;

The middle row deserves a comment, because it explains a classic moment of confusion. The mysql command-line client prints binary strings in hexadecimal notation by default (a setting called binary-as-hex), so a bare SELECT FROM_BASE64('aGVsbG8=') shows 68656C6C6F instead of hello. That is not a bug and not corruption; it is the client being cautious about binary data. If you want letters, either call HEX() deliberately, convert with CONVERT(... USING utf8mb4), or start the client with --binary-as-hex=0.

Now the rules the silent decoder applies. After ignoring whitespace, the remaining characters must form a multiple of four, every character must come from the standard alphabet (letters, digits, +, /, and =), and padding may only appear at the very end:

SELECT FROM_BASE64('aGVsbG8gd29ybGQ=') AS ok;
SELECT FROM_BASE64('aGVsbG8gd29ybGQ') AS missing_padding;
SELECT FROM_BASE64('!!!') AS nonsense;

All three lines run without complaint, and rows two and three return NULL. Missing padding, wrong length, alien characters: same shrug. Whitespace is the one indulgence; newlines, carriage returns, tabs and spaces are all ignored, which is a mercy for anything that passed through an e-mail first. The URL-safe alphabet, on the other hand, gets the shrug in return: an underscore is not in the standard table, so FROM_BASE64('yv7K_g==') is a NULL even though the length is a clean multiple of four. You have to translate the alphabet yourself before calling, and the URL-safe section below shows how.

One more trait worth knowing: the decoder and encoder are a matched pair. The encoder breaks its output into lines of 76 characters, and the decoder eats those line breaks for breakfast. If a column was filled by TO_BASE64() in this same database family, decoding is a perfect round trip. If it was filled by something else, keep reading.

PostgreSQL: The Decoder That Raises Its Voice

PostgreSQL has carried base64 in its core since at least version 7.4, back in 2005, which makes it the oldest base64 machinery in this family. The call is decode(string, 'base64'), and the result is bytea, the database's native binary type. The companion encode(bytea, 'base64') goes the other way and is mentioned here only because the two share one formatting contract: the RFC 2045 style, with lines broken at 76 characters. The decoder, for its part, ignores carriage returns, newlines, spaces and tabs anywhere in the input.

SELECT decode('aGVsbG8gd29ybGQ=', 'base64') AS bytes;
SELECT length(decode('aGVsbG8gd29ybGQ=', 'base64')) AS byte_count;
SELECT convert_from(decode('aMOpbGxv', 'base64'), 'UTF8') AS text;

The third row is the one you will reach for constantly: convert_from() turns the bytea into text in a named encoding, and it is the character set step the binary data needs (more on that in its own section later). aMOpbGxv comes back as héllo, accented character and all.

Where PostgreSQL separates itself from the field is the "when it breaks" column. Invalid input is a hard error, and the error message tells you exactly which rule broke:

• a character outside the alphabet: ERROR: invalid symbol "!" found while decoding base64 sequence
• a padding sign in the middle of the string: ERROR: unexpected "=" while decoding base64 sequence
• truncated input or missing padding: ERROR: invalid base64 end sequence, with the hint Input data is missing padding, is truncated, or is otherwise corrupted.
• a URL-safe underscore: ERROR: invalid symbol "_" found while decoding base64 sequence

For a data-cleaning job, that voice is a feature. The query fails, you see the row, you fix the source. The trade-off is that one poisoned row in a million stops the whole batch, so in production pipelines people often pre-filter with a regex before calling decode(). And a small display note: psql prints bytea as \x-prefixed hex, so \x68656c6c6f is the same "hello" the MySQL client shows as 68656C6C6F. Two dialects, two hex dialects.

SQL Server: The Late Arrival

Here is the surprise of the whole family. SQL Server shipped BASE64_DECODE() in version 2025, generally available in November 2025. Before that, the most popular database in enterprise land had no built-in base64 decoder for twenty-seven years, and the folklore was thick with workarounds. The modern function is a clean one: it takes a varchar(n) or varchar(max) expression and returns a varbinary (an n up to 6000 maps to varbinary(8000), anything larger maps to varbinary(max)), with NULL passing straight through.

SELECT BASE64_DECODE('aGVsbG8gd29ybGQ=') AS bytes;
SELECT CONVERT(VARCHAR(100), BASE64_DECODE('aGVsbG8gd29ybGQ=')) AS text;
SELECT BASE64_DECODE('yv7K_g') AS url_safe_also_works;

That third row is a genuinely nice touch: the decoder accepts both RFC 4648 alphabets, the standard one with + and / and the URL-safe one with - and _, and padding is optional. It also ignores the four whitespace characters (newline, carriage return, tab, space). When it does break, the error is Msg 9803, Level 16 with the text Invalid data for type "Base64Decode", and the State value tells you which rule you hit: state 20 for a character that is not in either alphabet, state 21 for characters that are all valid but arranged in a shape base64 cannot make, and state 23 for padding that appears too often or too early.

If you are stuck on a pre-2025 version, the classic workaround borrows the XML type, which has understood base64 since the XML Schema days:

SELECT CAST(N'' AS XML)
  .value('xs:base64Binary("aGVsbG8=")', 'VARBINARY(MAX)') AS legacy;

The XML engine base64-decodes the constant and hands back the bytes. It works, and it is what a generation of SQL Server developers used. It also has edges: the base64Binary type is strict about shape, so a MIME-wrapped string with line breaks inside it will not parse, and you are paying the XML machinery's price for a job a single function now does natively. Treat it as the museum piece it has become.

SQLite: The Dialect Without a Decoder

SQLite is the odd one out, and understanding why tells you how to use it. The core library is a small, embeddable engine, and base64 is not in its standard function list. If a column holds base64, the decoder has to come from one of four places: the command-line shell, a loadable extension, a custom function registered by the host application, or pure SQL. Here is each one.

The CLI. Starting with version 3.41.0 (February 2023), the sqlite3 command-line shell ships a base64() function. It decodes a text argument into a BLOB, which makes it perfect for exploratory work straight from a terminal:

$ sqlite3 app.db "SELECT hex(base64('aGVsbG8gd29ybGQ='));"
68656C6C6F20776F726C64

Two temperaments to know. First, it is lenient: characters it does not recognize are skipped rather than reported, so base64('!!!') returns an empty BLOB instead of an error. Great for curiosity, dangerous for auditing, because "empty" and "missing" look the same in the output. Second, the function is shape-shifting; a BLOB argument gets encoded into text (with 76-character lines), while a text argument gets decoded into a BLOB. The same name, two jobs, picked by the type of the argument. No other decoder in this family does that, so read your input type twice.

Pure SQL. The core library has no base64, but it has recursive CTEs, arithmetic, and (since 3.45.0) unhex(), which is enough to build a real decoder in a few dozen lines. The recipe: a 64-row alphabet table, the input cut into four-character chunks, each chunk turned into a 24-bit number, that number split into three bytes, and the bytes collected as hex before unhex() turns them into a BLOB. Here it is, working on a table column:

WITH RECURSIVE
b64(c, v) AS (
  SELECT 'A', 0 UNION ALL SELECT 'B', 1 UNION ALL SELECT 'C', 2
  UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 4 UNION ALL SELECT 'F', 5
  UNION ALL SELECT 'G', 6 UNION ALL SELECT 'H', 7 UNION ALL SELECT 'I', 8
  UNION ALL SELECT 'J', 9 UNION ALL SELECT 'K', 10 UNION ALL SELECT 'L', 11
  UNION ALL SELECT 'M', 12 UNION ALL SELECT 'N', 13 UNION ALL SELECT 'O', 14
  UNION ALL SELECT 'P', 15 UNION ALL SELECT 'Q', 16 UNION ALL SELECT 'R', 17
  UNION ALL SELECT 'S', 18 UNION ALL SELECT 'T', 19 UNION ALL SELECT 'U', 20
  UNION ALL SELECT 'V', 21 UNION ALL SELECT 'W', 22 UNION ALL SELECT 'X', 23
  UNION ALL SELECT 'Y', 24 UNION ALL SELECT 'Z', 25 UNION ALL SELECT 'a', 26
  UNION ALL SELECT 'b', 27 UNION ALL SELECT 'c', 28 UNION ALL SELECT 'd', 29
  UNION ALL SELECT 'e', 30 UNION ALL SELECT 'f', 31 UNION ALL SELECT 'g', 32
  UNION ALL SELECT 'h', 33 UNION ALL SELECT 'i', 34 UNION ALL SELECT 'j', 35
  UNION ALL SELECT 'k', 36 UNION ALL SELECT 'l', 37 UNION ALL SELECT 'm', 38
  UNION ALL SELECT 'n', 39 UNION ALL SELECT 'o', 40 UNION ALL SELECT 'p', 41
  UNION ALL SELECT 'q', 42 UNION ALL SELECT 'r', 43 UNION ALL SELECT 's', 44
  UNION ALL SELECT 't', 45 UNION ALL SELECT 'u', 46 UNION ALL SELECT 'v', 47
  UNION ALL SELECT 'w', 48 UNION ALL SELECT 'x', 49 UNION ALL SELECT 'y', 50
  UNION ALL SELECT 'z', 51 UNION ALL SELECT '0', 52 UNION ALL SELECT '1', 53
  UNION ALL SELECT '2', 54 UNION ALL SELECT '3', 55 UNION ALL SELECT '4', 56
  UNION ALL SELECT '5', 57 UNION ALL SELECT '6', 58 UNION ALL SELECT '7', 59
  UNION ALL SELECT '8', 60 UNION ALL SELECT '9', 61 UNION ALL SELECT '+', 62
  UNION ALL SELECT '/', 63
),
chunks AS (
  SELECT name, b64, (LENGTH(b64) + 3) / 4 AS n
  FROM payload
),
seq(name, n, i) AS (
  SELECT name, n, 1 FROM chunks
  UNION ALL
  SELECT name, n, i + 1 FROM seq WHERE i < n
),
vals AS (
  SELECT s.name, s.i AS chunk_no, s.n,
         COALESCE((SELECT v FROM b64 WHERE c = substr(ch.b64, (s.i - 1) * 4 + 1, 1)), -1) AS v1,
         COALESCE((SELECT v FROM b64 WHERE c = substr(ch.b64, (s.i - 1) * 4 + 2, 1)), -1) AS v2,
         COALESCE((SELECT v FROM b64 WHERE c = substr(ch.b64, (s.i - 1) * 4 + 3, 1)), -1) AS v3,
         COALESCE((SELECT v FROM b64 WHERE c = substr(ch.b64, (s.i - 1) * 4 + 4, 1)), -1) AS v4
  FROM seq s
  JOIN chunks ch ON ch.name = s.name
),
hexes AS (
  SELECT name, chunk_no, n,
         (CASE WHEN v1 < 0 THEN 0 ELSE v1 END) * 262144 +
         (CASE WHEN v2 < 0 THEN 0 ELSE v2 END) * 4096 +
         (CASE WHEN v3 < 0 THEN 0 ELSE v3 END) * 64 +
         (CASE WHEN v4 < 0 THEN 0 ELSE v4 END) AS v24,
         CASE WHEN v2 >= 0 OR v3 >= 0 THEN 1 ELSE 0 END +
         CASE WHEN v3 >= 0 OR v4 >= 0 THEN 1 ELSE 0 END +
         CASE WHEN v4 >= 0 THEN 1 ELSE 0 END AS n_bytes
  FROM vals
),
acc(name, n, i, hx) AS (
  SELECT h.name, h.n, 1,
         (CASE WHEN h.n_bytes >= 1 THEN printf('%02X', h.v24 / 65536) ELSE '' END) ||
         (CASE WHEN h.n_bytes >= 2 THEN printf('%02X', (h.v24 / 256) % 256) ELSE '' END) ||
         (CASE WHEN h.n_bytes >= 3 THEN printf('%02X', h.v24 % 256) ELSE '' END)
  FROM hexes h
  WHERE h.chunk_no = 1
  UNION ALL
  SELECT a.name, a.n, a.i + 1,
         a.hx || (
           SELECT (CASE WHEN h.n_bytes >= 1 THEN printf('%02X', h.v24 / 65536) ELSE '' END) ||
                  (CASE WHEN h.n_bytes >= 2 THEN printf('%02X', (h.v24 / 256) % 256) ELSE '' END) ||
                  (CASE WHEN h.n_bytes >= 3 THEN printf('%02X', h.v24 % 256) ELSE '' END)
           FROM hexes h
           WHERE h.name = a.name AND h.chunk_no = a.i + 1
         )
  FROM acc a
  WHERE a.i < a.n
)
SELECT name, unhex(hx) AS restored
FROM acc
WHERE i = n;

Run it against a table with a b64 column and you get a BLOB per row, no extensions, no application code. The arithmetic is plain base64 in integer clothing: each of the four characters contributes six bits, the middle two characters straddle a byte boundary, and the last character's low two bits are discarded. It is the slowest option on this page (a recursive pass plus a lookup per chunk), so keep it for small payloads and one-off archaeology. For a long-running application, the honest answer is the third option: register a one-line custom function from the host language (Python's sqlite3 module does it in two lines with create_function() and the standard base64 module) and let the engine call it like a native. The fourth option, loadable extensions such as the sqlean family, exists too, but it means installing a different engine build, which most teams would rather avoid.

DuckDB: Strict, Small, Opinionated

DuckDB is an analytical database with a real binary type, BLOB, and a tidy family of blob functions around it. The decoder is from_base64(string), and it sits next to its friends to_base64(), hex(), md5() and sha256() in the same reference page, which is where most DuckDB users first meet it.

SELECT from_base64('aGVsbG8gd29ybGQ=') AS bytes;
SELECT decode(from_base64('aMOpbGxv')) AS text;
SELECT hex(from_base64('AAEC')) AS padding_optional;

The third row shows a friendlier rule than you might expect: when the length is a multiple of four, missing padding is no problem, AAEC decodes to the bytes 00 01 02 just fine. The strictness shows up the moment the shape is wrong. DuckDB wants a length that is a multiple of four, full stop, and the conversion error says exactly that:

SELECT from_base64('YWJ');
-- Conversion Error: Could not decode string "YWJ" as base64: length must be a multiple of 4

Two more opinions to respect. First, DuckDB's decoder speaks only the standard alphabet; an underscore is not a character it recognizes, so URL-safe tokens must be translated before they arrive (the recipe is in the URL-safe section). Second, it has no tolerance for whitespace at all. A MIME-wrapped e-mail attachment with its 76-character line breaks inside will fail, and the fix is a replace() over newlines and carriage returns before the call. And because there is no try_ variant to soften the blow, the gentle pattern is a pre-check in the same query:

SELECT CASE
         WHEN b64 ~ '^[A-Za-z0-9+/]*={0,2}$'
          AND MOD(LENGTH(b64), 4) = 0
         THEN from_base64(b64)
       END AS maybe_bytes
FROM attachments;

Regex first, decoder second: the query returns NULL for anything that cannot possibly decode, and the decoder only ever sees well-shaped input.

ClickHouse: The Column Decoder

ClickHouse has no separate binary type; its String is happily binary-safe, which means decoding "into a string" is the whole job and no conversion step follows. The function has been around since version 18.16.0 (2019) under the name base64Decode(), and it keeps a MySQL-style alias, FROM_BASE64(), so ported queries need no rewriting.

SELECT base64Decode('aGVsbG8gd29ybGQ=') AS text;
SELECT tryBase64Decode('definitely not base64') AS gentle;
SELECT base64URLDecode('aHR0cHM6Ly9jbGlja2hvdXNlLmNvbQ') AS url;

The second row is the ClickHouse house style in action. The engine loves its try prefix: tryBase64Decode() swallows the failure and returns an empty string, while plain base64Decode() throws an exception with the code INCORRECT_DATA and a message naming the offending value. Pick the plain form when a bad row should stop the pipeline and the try form when the report should keep going, and pick it deliberately, not by accident.

Two version notes, because ClickHouse moves fast. Before 26.7, whitespace in the input was rejected; from 26.7 on, space, tab, line feed, carriage return and form feed are all ignored, which is the behavior you want for anything that touched an e-mail or a text editor. And the modern decoder expects proper padding on its four-character groups, so a token that lost its equals signs on the way in will be an exception rather than a best effort. If a query that worked in 2023 starts throwing in 2026, look at the server version before you blame the data.

Oracle: RAW or Nothing

Oracle's base64 machinery lives in the UTL_ENCODE PL/SQL package, and it has a personality of its own: it takes RAW and returns RAW, nothing else. No text in, no text out. VARCHAR2 is character data with a character set; RAW is bare bytes; and the package refuses to pretend otherwise. So the working pattern is a three-layer sandwich, cast to raw, decode, cast back to text:

SELECT UTL_RAW.CAST_TO_VARCHAR2(
         UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW('aGVsbG8gd29ybGQ='))
       ) AS restored
FROM DUAL;

Every step earns its place. UTL_RAW.CAST_TO_RAW() reinterprets the text's bytes as raw (in the database character set, which for a modern deployment is usually AL32UTF8, so your UTF-8 input travels as-is). UTL_ENCODE.BASE64_DECODE() does the actual work. And UTL_RAW.CAST_TO_VARCHAR2() reinterprets the result bytes as text in that same database character set. Skip any layer and you get a type-mismatch error, which is Oracle doing its job of being explicit.

Invalid input raises a PL/SQL exception rather than a quiet NULL, so a batch decode should live inside an exception handler that logs the offending row. The package also carries a whole museum of sibling decoders: MIME header decoding, quoted-printable, uudecode, text encoding, all from the same era. You will mostly use the base64 pair, but the neighbors explain why the package is organized the way it is: Oracle wanted one home for "data wearing a transport costume".

One size trap to know before you start. In plain SQL, a RAW value is capped at 2000 bytes, so a base64 column holding more than about 1500 raw bytes cannot be decoded with a single SELECT statement at all. Larger payloads need a PL/SQL loop that walks the BLOB in chunks of 2000 (or fewer) bytes, decodes each piece, and stitches the results back together. It is old-school, but it is the standard Oracle answer, and it is one of those places where the language's 1990s type system still shapes your 2020s queries.

Snowflake: Bring Your Own Alphabet

Snowflake separates its binary type (BINARY) from its text types, and it gives you the most configurable decoder in this family. The workhorse is BASE64_DECODE_BINARY(input), which returns BINARY, and the optional second argument is a short string that redefines the alphabet:

SELECT BASE64_DECODE_BINARY('aGVsbG8gd29ybGQ=') AS bytes;
SELECT TO_VARCHAR(BASE64_DECODE_BINARY('aMOpbGxv'), 'UTF-8') AS text;
SELECT TO_VARCHAR(BASE64_DECODE_BINARY('aHR0cHM6Ly9jbGlja2hvdXNlLmNvbQ', '-_'), 'UTF-8') AS url_safe;

Read that alphabet argument carefully, because it is positional. Up to three characters are allowed: the first two override alphabet positions 62 and 63 (the defaults are + and /), and the third overrides the padding character (default =). To say "use the URL-safe alphabet" you pass '-_'. To say "URL-safe alphabet but pad with % instead" you must pass all three characters, '-_%', even though the first two are just restating the new defaults. Omit characters and you keep the defaults; you cannot skip a position and fill the next one.

Two companions finish the set. BASE64_DECODE_STRING() does the decode and the text conversion in one call, so you can skip the TO_VARCHAR() when the payload is text. And the TRY_ variants, TRY_BASE64_DECODE_BINARY() and TRY_BASE64_DECODE_STRING(), return NULL on a bad value instead of raising an error, which is the Snowflake version of ClickHouse's try form. One last note for people reading old scripts: you may find calls to a bare BASE64_DECODE(). That name predates the explicit binary/string pair and does the same job as BASE64_DECODE_BINARY(); new code should use the explicit name.

Bytes to Text: The Character Set Step

Decoding hands you bytes. If the payload is a document, a name, a JSON fragment, you owe it one more step: an interpretation as text in a named character set. This is where "it decoded but looks wrong" comes from, because a byte sequence only becomes words once you say which language of bytes you are reading. The table is short and worth memorizing:

The spread is wide on purpose. PostgreSQL and DuckDB validate and refuse, which protects your downstream code from mojibake. MySQL and Oracle reinterpret silently, which is fast but means the database cannot save you from a Latin-1 payload arriving in a UTF-8 world. SQLite does not even look, because in SQLite a TEXT value is just bytes with a tag. The practical rule: decide the character set before you decode, write it into the query as a literal, and test with a payload that contains a non-ASCII character (the classic aMOpbGxv for héllo is a good canary, because it breaks differently in every wrong character set). For genuinely binary payloads, skip this section entirely and keep the bytes as bytes.

JWTs: Three Dots of Base64 in a Column

Json Web Tokens are the most common base64 you will find sitting in a database, because authentication events get logged with their tokens. A JWT is three dot-separated pieces: a header, a payload, and a signature. The first two are JSON objects packed as base64, and here is the twist that catches people out: JWTs use the URL-safe alphabet without padding, not the standard padded form. The dots would collide with a /, the URL context would mangle a +, and the padding equals signs would be pure ceremony, so the spec (RFC 7515 and RFC 7519) switched to - and _ and dropped the padding.

Decoding a token in SQL is therefore a four-step dance: split on the dots, swap the URL-safe characters back to the standard alphabet, restore the padding, decode and parse the JSON. PostgreSQL, with its JSONB type, is a comfortable place to do it:

WITH parts AS (
  SELECT split_part('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkRldiBVc2VyIiwiaWF0IjoxNTE2MjM5MDIyfQ.pDDL2Ljz7cK7vo9Ne4sMFMec1JMhasDUWa82vl-fYsw', '.', 1) AS header_b64,
         split_part('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkRldiBVc2VyIiwiaWF0IjoxNTE2MjM5MDIyfQ.pDDL2Ljz7cK7vo9Ne4sMFMec1JMhasDUWa82vl-fYsw', '.', 2) AS payload_b64
)
SELECT convert_from(
         decode(replace(replace(payload_b64, '-', '+'), '_', '/')
           || CASE MOD(LENGTH(payload_b64), 4)
                 WHEN 2 THEN '=='
                 WHEN 3 THEN '='
                 ELSE '' END,
           'base64'),
         'UTF8')::jsonb AS claims
FROM parts;

The result is a JSONB value you can query like any other column, and for the token above it comes back as {"iat": 1516239022, "sub": "1234567890", "name": "Dev User"}. Pulling a single claim out of the result is then just claims->>'sub' in a follow-up query. The padding restoration is the CASE expression: a base64url string whose length is two short of a multiple of four needs two equals signs, three short needs one, and an exact multiple needs none.

Go one step further and you can even verify an HS256 signature in SQL, using PostgreSQL's pgcrypto extension for the HMAC (enable it once with CREATE EXTENSION IF NOT EXISTS pgcrypto; if it is not already on). Recompute the signature over header.payload with the shared secret, format it the same base64url way, and compare:

WITH parts AS (
   SELECT split_part('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkRldiBVc2VyIiwiaWF0IjoxNTE2MjM5MDIyfQ.pDDL2Ljz7cK7vo9Ne4sMFMec1JMhasDUWa82vl-fYsw', '.', 1) AS header_b64,
          split_part('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkRldiBVc2VyIiwiaWF0IjoxNTE2MjM5MDIyfQ.pDDL2Ljz7cK7vo9Ne4sMFMec1JMhasDUWa82vl-fYsw', '.', 2) AS payload_b64
 )
 SELECT rtrim(replace(replace(
          encode(hmac((header_b64 || '.' || payload_b64)::bytea,
                      'sql-secret-key'::bytea, 'sha256'), 'base64'),
            '+', '-'),
            '/', '_'),
          '=') = 'pDDL2Ljz7cK7vo9Ne4sMFMec1JMhasDUWa82vl-fYsw' AS valid
 FROM parts;

The hmac() call produces the digest, encode(..., 'base64') packs it, and the three string operations reshape it into the no-padding URL-safe form the token carries. For the token and secret above, the answer is a cheerful t. Keep the caveats with you, though: this only works for HMAC algorithms (HS256, HS384, HS512), it puts a shared secret inside a database statement, and it is built for reporting, auditing and debugging. Anything that actually gates access should verify in the application layer with a real JWT library.

Data URLs: The Image Inside a String

The data URL format (RFC 2397) is the web's way of inlining a file into a link: data:image/png;base64, followed by the file's base64. Browsers paste them from the clipboard, single-page apps embed small images in them, and every one of those flows eventually lands in a database column as a long text value. The format is data:{media type}[;{parameters}][;base64],{data}, and the only part that matters for decoding is everything after the first comma, because that is where the base64 payload begins.

SELECT uri,
       CAST(FROM_BASE64(SUBSTRING(uri, LOCATE(',', uri) + 1)) AS BINARY) AS png_bytes
FROM uploads
WHERE uri LIKE 'data:image/png;base64,%';

That is the whole job in MySQL: find the comma, skip past it, decode, and you hold the image bytes in a binary expression you can store in a BLOB column or hash for deduplication. Other dialects swap the functions (SUBSTR() and INSTR() in most, substring() and position() in others) but the shape is identical.

Three warnings. First, not every data URL is base64; a data URL without the ;base64 marker carries percent-encoded text instead, and feeding it to a base64 decoder is a mistake the LIKE filter above is there to prevent. Second, the media type in the prefix is a claim, not a fact; the same string can say image/png and contain a JPEG. If the content matters, check the magic bytes of the decoded result (PNG starts with 89 50 4E 47, JPEG with FF D8). Third, data URLs are big. A 4-megapixel photo becomes roughly a 5.5-megabyte string, which is a column-size and a memory conversation, not a string-function one.

URL-safe Base64: The Alphabet That Travels

Section 5 of RFC 4648 defined a second alphabet for base64 because the original one has two characters with jobs in URL syntax. The plus sign is how query parameters add values, the slash is how paths are separated, and the equals sign of padding gets percent-encoded the moment it meets a query string. The URL-safe variant swaps + for - and / for _ (both harmless in URLs), and the JWT spec on top of that drops the padding entirely. The result travels through links, path segments, file names and fragment identifiers without a single percent sign.

You will meet it in a database mostly because tokens and links were stored, not because the data was born there. Here is who can handle it natively and who needs the two-minute manual:

The manual is two REPLACE() calls plus padding restoration, and it is the same on every dialect. In PostgreSQL it reads like this:

SELECT convert_from(
         decode(replace(replace('aGVsbG8', '-', '+'), '_', '/')
           || CASE MOD(LENGTH('aGVsbG8'), 4)
                 WHEN 2 THEN '=='
                 WHEN 3 THEN '='
                 ELSE '' END,
           'base64'),
         'UTF8') AS text;

Swap - back to +, swap _ back to /, append the missing padding based on the length modulo four, and the standard decoder takes over from there. The input aGVsbG8 (the no-padding URL-safe form of "hello") comes back as the word itself. The two mistakes that keep happening are the ones the CASE expression prevents: forgetting the padding, which makes strict decoders reject a length that is not a multiple of four, and skipping the character translation, which makes a decoder that does not know the URL-safe alphabet choke on the underscore. Write the translation once, as a reusable function in your database, and the whole problem stops recurring.

Files, Blobs and Big Things

Decoding is how files get out of columns, and every dialect has a slightly different exit door. In DuckDB the round trip is two statements, one to read a file into a BLOB and one to write decoded bytes back out:

SELECT filename, octet_length(content) AS size
 FROM read_blob('/data/uploads/*.png');

The reading side: read_blob() is a table function that accepts a file name, a list of names or a glob pattern and hands back a filename and a content column per file. The writing side is its own statement: COPY with the BLOB format writes raw bytes, no quoting, no escaping, exactly what a decoded payload wants.

COPY (SELECT from_base64(b64) FROM attachments WHERE id = 42)
 TO '/data/restored/cat.png' (FORMAT BLOB);

PostgreSQL's exit door is the large object API. A large object is a server-side binary chunk store addressed by an OID, and lo_export() writes one out to a file on the database server. It requires superuser rights or the pg_write_server_files privilege, and the destination must be a path the server process can write to, so in practice it is a job for maintenance scripts rather than application code:

SELECT lo_export(12345, '/tmp/attachments/cat.png');

MySQL and SQL Server have no plain-SQL file writer at all (writing to disk is a client or agent job, via the export tooling of each), which is a fair design: the database stores the bytes, the application decides where the file belongs. SQLite sits at the other end of the spectrum, where the application is the host and a BLOB column can be written straight to disk in one call of the host language.

Then there are the ceilings, which differ more than you would expect from databases that all pretend to be alike:

The MySQL row deserves a story, because it is the one that surprises people in production. max_allowed_packet caps the size of a single packet between client and server, and a base64 string is part of that packet. A 50-megabyte photo encoded to base64 is about a 67-megabyte string, which is larger than the 64-megabyte default, and the result is not an error you can read in the query: it is a truncated or NULL value that looks like data corruption. If you are moving large files through a MySQL column, check that limit before you start, and remember that the encoded form, not the raw bytes, is what counts against it.

Email Wraps and MIME Lines

Any base64 that has survived the e-mail system carries a souvenir: line breaks. MIME, the set of standards that lets e-mail carry binary attachments (RFC 2045, section 6.8), wraps base64 output at 76 characters and ends the lines with a carriage return and a line feed. The wrap exists because the old e-mail network could not trust lines longer than that, and the format has been carried forward out of habit ever since. So an attachment stored in a database column is frequently a base64 string with a line break every 76 characters, and your decoder's relationship with those line breaks decides whether the job is one statement or two.

The "strip first" fix is one expression, and it is always safe, because whitespace is not part of the base64 alphabet: no legitimate payload can contain a space, tab or line break, so removing them cannot destroy information. In PostgreSQL the idiom is a single regexp_replace():

SELECT decode(regexp_replace(attachment_b64, '\s', '', 'g'), 'base64')
FROM email_attachments;

Every whitespace character, line breaks included, goes, and the decoder sees one clean continuous string. Run this in DuckDB (with its replace() over the two line-break characters) or in a pre-26.7 ClickHouse, and the wrapped attachment decodes exactly like the unwrapped one.

API Payloads, Configs and Auth Headers

Step back from the individual functions and a pattern appears: base64 in a database column is almost always one of three things. A field inside a JSON document (an image, a certificate, a file that an API decided to inline). A configuration value (a secret or a credential that some tool prefers in base64, because base64 fits on one line of a YAML file with no quotes, no newlines and no backslashes). Or an authentication artifact (a Basic auth header, a stored token, a session blob). Here is each one with its decoding shape.

JSON fields. The JSON arrived as text, the field is a string, and the base64 is hiding inside it. Extract the field with the JSON function of your dialect, then decode. In MySQL the whole chain is one expression:

SELECT event_id,
       CAST(FROM_BASE64(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.image'))) AS BINARY) AS image_bytes
FROM api_events
WHERE JSON_TYPE(JSON_EXTRACT(payload, '$.image')) = 'STRING';

PostgreSQL does the same with JSONB, where the field comes out as text with the ->> operator and decode() takes over. The JSON_TYPE guard on the last line matters more than it looks: it keeps the decoder away from rows where the field is a number, a nested object or missing, and in MySQL those rows would otherwise contribute a silent NULL to your count of "how many events had an image".

Authentication headers. A Basic auth header is the literal string Basic followed by the base64 of username:password. Decoding it in SQL is a substring and a split, which is exactly why people do it (usually to audit which users hit which endpoints, not to verify the password, which the database should never see in cleartext):

SELECT request_id,
       SUBSTRING_INDEX(CAST(FROM_BASE64(SUBSTRING(header_value, 7)) AS CHAR), ':', 1) AS username,
       SUBSTRING_INDEX(CAST(FROM_BASE64(SUBSTRING(header_value, 7)) AS CHAR), ':', -1) AS secret
FROM http_log
WHERE header_name = 'Authorization'
  AND header_value LIKE 'Basic %';

SUBSTRING(header_value, 7) peels off the Basic prefix, the decoder restores the original text, and the two SUBSTRING_INDEX() calls split it at the colon, first part for the user, last part for the secret. In PostgreSQL the same query uses substring() and split_part().

Configuration values. The decode direction here is the audit job: someone stored a secret as base64 in a config table (a habit inherited from Kubernetes, where secret values are base64 at rest), and you want to see what is actually in there, or you are building the export that a new environment will consume. The shape is one SELECT per value, and the character set step applies if the value is text:

SELECT name,
       CONVERT(FROM_BASE64(value) USING utf8mb4) AS plaintext
FROM app_config
WHERE name LIKE '%_secret%';

Treat that result with the care it deserves. You just turned stored secrets into visible query output; make sure the account running the query has the rights it should, that the result is not copied into a log, and that the base64-in-config habit gets a second look. Base64 is a transport, not a vault, and an audit query is the moment where that becomes obvious.

The Pitfalls That Bite

Every pitfall on this list is one that has taken an afternoon in at least one codebase, and every one of them is specific to the way SQL dialects handle base64 rather than to base64 itself.

• The silent NULL. MySQL and MariaDB decode bad input to NULL without complaint. In a report that joins on the decoded value, those rows simply vanish, and the difference between "0 rows" and "0 rows because 14 of them were poisoned" is invisible until someone asks why the count does not add up. If your decoder is the quiet kind, count your NULLs on purpose.
• The multiple-of-four rule, applied unevenly. A string whose length is not a multiple of four is not base64, but the dialects disagree about what to do: PostgreSQL raises an error, DuckDB raises a conversion error, ClickHouse raises an exception, MySQL returns NULL, and the SQLite CLI quietly decodes what it can. The same data file produces five different outcomes on five databases, which is why "it worked in Postgres" is not a test.
• The alphabet mismatch. A URL-safe token (JWT, link, file name) fed to a standard-alphabet decoder: SQL Server accepts it, ClickHouse's base64URLDecode() accepts it, Snowflake accepts it with the right argument, and everyone else either returns NULL, raises an error or, in the SQLite CLI's case, silently drops the underscore and its two neighbors and hands you the wrong bytes. The wrong-bytes case is the nasty one, because the result looks plausible.
• The MIME wrap. Wrapped input into a decoder that does not eat line breaks (DuckDB, pre-26.7 ClickHouse, Oracle) fails, and the failure often looks like "the last 76 characters are garbage" rather than "there is a newline in here", because the error points at the character after the break.
• The display trick. The mysql client prints binary as hex, psql prints bytea as \x-hex, Snowflake prints BINARY as hex, and Oracle prints RAW as hex. Four clients, four hex notations, one very human mistake of concluding the data is corrupted because the screen shows numbers. Always convert explicitly before you read the result with your eyes.
• Padding in the wrong place. An equals sign is only legal at the end, one or two of them. A string like YQ==BQ== is two valid groups wearing one costume, and the strict decoders reject it while the lenient ones decode it into something nobody asked for. If you ever see padding in the middle of a stored value, the encoder that wrote it is broken, and fixing the data is a one-off job.
• The character set surprise. Decoding succeeds, the text comes back, and the accents are wrong. The bytes were fine; the interpretation was not. This is the CONVERT(... USING latin1) that should have been utf8mb4, the CAST(bin AS VARCHAR) that ran under a collation that swallows invalid sequences, the CAST(blob AS TEXT) in SQLite that never checks. Pin the character set as a literal in the query and test with an accented canary.
• The ceilings. Oracle's 2000-byte RAW limit in SQL statements, MySQL's max_allowed_packet taxing the encoded size, PostgreSQL's 1-GB bytea ceiling, Snowflake's 8-MB BINARY habit. Each one is documented, each one is discovered in production, and each one is a size check you could have written before the data was big.
• Trusting the decoded bytes. Base64 can carry anything, including a string full of quotes. Decoding is not sanitizing. Whatever you do with the decoded text (compare it, log it, concatenate it into another statement) still needs the usual protections, and a parameterized query is still a parameterized query after a base64 round trip.

How to Stay on the Right Side

• Decide the type first, not the function first. Is the payload binary or text? Binary goes to BLOB/bytea/varbinary and stays there. Text goes through the character set step with an explicit encoding. Half of all base64 pain in SQL is a binary payload that wandered into a text column (or vice versa) and is now being interpreted.
• Validate before you decode, or decode gently. A regex over the alphabet plus a length-modulo-four check costs nothing and turns a batch-stopping error into a NULL you can count. Where the dialect offers a try form (ClickHouse's tryBase64Decode, Snowflake's TRY_BASE64_DECODE_BINARY), use it for reporting and keep the strict form for pipelines that must not guess.
• Version-check the dialect, not just the database. ClickHouse 26.7 changed whitespace handling, SQL Server 2025 is the first release with the function at all, the SQLite CLI needs 3.41, and ClickHouse's padding expectations tightened over time. "It is ClickHouse" is not a specification; "it is ClickHouse 24.8" is.
• Document the alphabet of each column. A column that can hold both standard and URL-safe base64 is a column that will confuse the next developer. If the data comes from JWTs, say so in the schema comment; if it comes from MIME attachments, say that too. The decoder choice is a property of the column, not of the query.
• Store bytes, encode at the edge. If you control the schema, a BLOB column plus encoding in the API layer beats a base64 text column for storage, for indexing and for every future query. Base64 in the column is a compatibility tax, and taxes are best paid once, at the boundary.
• Round-trip with a canary. Before trusting a new decode path, push a known payload through encode and decode in the same database and compare. The canary should contain a non-ASCII character (to exercise the character set step), a length that leaves a padding tail (to exercise the padding rules), and, for URL-safe paths, a - or _ somewhere (to exercise the alphabet translation).
• Keep secrets out of the query text. JWT verification with pgcrypto puts a shared secret in the statement; config audits put cleartext secrets in the result. Both are legitimate jobs, but they deserve a restricted account, a clean log, and a review, not a production connection string and a SELECT * INTO OUTFILE.

A Short History of Unpacking in SQL

The base64 format itself is older than the internet's useful part. It was standardized for MIME in the mid-1990s (RFC 2045, section 6.8, building on the 1993 RFC 1421), and the name is just a count: the alphabet has 64 characters. The URL-safe variant arrived with RFC 4648 in 2006, and the JWT spec in 2015 made that variant the one you actually see in token columns. But the databases each met the format on their own schedule, and the schedule tells you something about each one's soul.

2005. PostgreSQL 7.4 already lists base64 as a first-class format of encode() and decode(), which makes it the oldest base64 support in this family by a wide margin. A database with a real binary type and a format argument got there early, because the answer was one enum value away.

Early 2000s. Oracle's UTL_ENCODE package appears in the 9i era, carrying base64 next to MIME header, quoted-printable and uuecode functions. It is RAW in and RAW out, which is very Oracle, and it has kept that shape for three decades.

2013. MySQL 5.6 adds TO_BASE64() and FROM_BASE64(), and MariaDB 10.0 carries both into the fork. The pair encodes with 76-character lines and decodes with whitespace tolerance, a matched set that has not changed in a dozen major versions.

2019. ClickHouse 18.16 ships base64Decode() with its MySQL-style alias, because the columnar world was importing workloads that already carried base64 in their log schemas.

2023. SQLite 3.41.0 adds base64() and its base85 sibling to the command-line shell as application-defined functions. The core library, true to form, gets nothing; the shell, which is where humans actually poke at SQLite databases, gets the tool.

2025. SQL Server 2025, generally available in November 2025, adds BASE64_DECODE() and BASE64_ENCODE() to T-SQL after a twenty-seven-year absence. The release notes treat them as a modest feature; the community treats them as a rescue.

The pattern is clean once you see it. Databases with a genuine binary type and a format argument (PostgreSQL, and in its way Oracle) got base64 the day the need was obvious. Databases where everything is a string (MySQL, SQL Server) treated it as a string convenience and scheduled it accordingly. And the embeddable engine (SQLite) still considers it the host application's job, with the CLI as a friendly exception.

Things That Will Make You Smile

• SQL Server spent 2000 to 2025 without a base64 decoder, and the community's answer was an XML function called xs:base64Binary() inside a CAST(N'' AS XML). An entire generation of enterprise queries decoded tokens through the XML parser, because the XML parser had understood base64 since 2001 and the SQL engine had not.
• The SQLite CLI's base64() is the only shape-shifter in this family: pass it a BLOB and it encodes, pass it text and it decodes. The function changes its job based on the type of its argument, which is a small act of SQL telepathy and a genuine trap for the unwary.
• PostgreSQL's encoder wraps at 76 characters exactly like the 1995 MIME standard, except it ends the lines with a lone newline instead of the standard's carriage return and newline. Twenty years after the spec, one fewer character. The decoder ignores both, so the rebellion is invisible unless you diff the output.
• In the mysql client, SELECT FROM_BASE64('aGVsbG8=') prints 68656C6C6F. Not because the data is hex, and not because anything is wrong, but because the client decided, on your behalf, that binary strings should be displayed as hex. The setting is called binary-as-hex, and it has convinced thousands of developers their decoder is broken.
• Oracle's SQL-level RAW type is capped at 2000 bytes, so a 3-kilobyte certificate cannot even be pasted into a SQL statement as a RAW literal. The decode has to happen in PL/SQL, in chunks, with a loop. The limit dates from the 1990s; the loop is still the recommended answer.
• Snowflake displays BINARY values as hex in every result set, so a perfectly successful decode of "hello" arrives on your screen as 68656C6C6F. Two dialects, two hex displays, one identical feeling of unease.
• ClickHouse keeps the alias FROM_BASE64() next to its native base64Decode(), a small courtesy to the MySQL refugees who arrived with queries that would otherwise not run.
• The whole family shares one quiet fact: base64 is a 33 percent tax on the way out and a 25 percent refund on the way in, and none of the eight decoders here will tell you that without being asked. The format is a costume; the wardrobe is free; the tailoring is what this article is about.

Keep Going

This article has been about taking the disguise off: the function in each dialect, its temperament, and the payloads (JWTs, data URLs, wrapped e-mail, JSON fields, config values, auth headers) that wear it. The other direction is its own animal, with its own set of surprises: which encoders wrap their output at 76 characters and which do not, how to produce the URL-safe no-padding form that tokens expect, the size math that decides your column width, and what SQL Server's 27-year gap means for anyone still on an older version. All of that, from TO_BASE64() to BASE64_ENCODE(), is covered in depth in the related Base64 encoding article for SQL, linked from this page's article list. Decode here, encode there, and the whole round trip fits inside one afternoon.

Last updated: 2026-08-30

Related article: Base64 Encoding in SQL: A Complete Guide