Shape / columns · All operations

Convert binary and encoded text

Base64, hex, blob and character-set conversion.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Base64 and hex, there and back

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
SELECT customer, to_base64(encode(customer)) AS b64, decode(from_base64(to_base64(encode(customer)))) AS back, hex(customer) AS hx FROM orders LIMIT 3;
SELECT CASE WHEN count(*) FILTER (WHERE decode(from_base64(to_base64(encode(customer)))) <> customer) = 0 THEN 'round trip holds'
            ELSE error('base64 round trip changed a value') END AS verdict FROM orders;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “base64 round trip changed a value”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Work with bit strings

Create, combine and inspect strings of bits.

Convert the character set

Re-encode text from one character set to another.

Encode or decode base64 and hex

Convert values to and from base64, base32, hex or a binary blob.

Escape text for a format

Escape or unescape special characters for JSON, XML, CSV or HTML.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Base64 Encoderread 2026-09-27

    Encode a string field as Base64

  • Blob Convertread 2026-09-27

    Convert fields to a binary large object or a blob back to another type

Apache DataFusion (1)
Apache NiFi (3)
  • ConvertCharacterSetread 2026-09-27

    Convert content from one character set to another

  • Encode/Decode Functionsread 2026-09-27

    17 functions: escapeJson, escapeXml, escapeCsv, escapeHtml3, escapeHtml4, …

  • EncodeContentread 2026-09-27

    Encode or decode content as Base64, Base32 or hex

DuckDB (2)
Duckle (1)
  • Base64read 2026-09-25

    encode a column to base64 or decode it back

Polars (2)
  • Expr.bin (group)read 2026-09-27

    11 expressions: contains, starts_with, encode, decode, size and others; test, slice and re-encode binary values

  • Expr.str: encode and decode (group)read 2026-09-27

    2 expressions: encode, decode; convert strings to and from an encoding such as hex or base64

Background

Base encodings such as base64 and hex are used to store or transfer binary data where a system is restricted to US-ASCII text.[1] Text decoded with an unintended character encoding comes out garbled, a result known as mojibake.[2]

When to use it

Base encoding can also be used where there is no legacy restriction, simply because it makes it possible to manipulate binary objects with text editors.[1] Base64 encoding increases the size of the data by 33%, plus about 3% more if line breaks are inserted at a typical line length.[3] Keep bytes in a binary column wherever the system allows it, and encode them only at the boundary that needs text, such as a CSV export, a URL or a JSON payload. (Meridian's view)

DuckDB's decode function, which converts a BLOB to VARCHAR, throws an error on invalid UTF-8 by default, and can instead replace invalid characters with ? or remove them.[4] Leave the error on while you are still learning a source: a failed query tells you the bytes are not in the encoding you assumed, while a replaced or dropped character hides it. (Meridian's view)

Mojibake is often seen with text data tagged with a wrong encoding, or not tagged at all and moved between computers with different default encodings.[2]

Base64 with the URL and filename safe alphabet, called base64url, should not be regarded as the same encoding as base64.[1] Padding with = is not always used in base-encoded data, but where assumptions about the size of the data cannot be made, padding is required to decode it correctly.[1] Before decoding a token taken from a URL or a web token, check which alphabet produced it and whether its padding was stripped. (Meridian's view)

The method

Request for Comments (RFC) 4648, The Base16, Base32, and Base64 Data Encodings, was written by S. Josefsson and published in October 2006, obsoleting RFC 3548.[1] Base64 treats each 24-bit group of input, three 8-bit groups, as four 6-bit groups, and translates each 6-bit group into one character of a 64-character alphabet.[1] Base32 uses a 33-character subset of US-ASCII, enabling 5 bits to be represented per printable character.[1] Base 16 encoding is the standard case-insensitive hex encoding, which writes each 8-bit group of input as 2 characters from a 16-character alphabet.[1] Implementations of base encodings must reject encoded data containing characters outside the base alphabet, unless the specification referring to RFC 4648 explicitly states otherwise.[1]

The Multipurpose Internet Mail Extensions (MIME) specification lists Base64 as one of two binary-to-text encoding schemes, the other being quoted-printable.[3] The first known standardised use of the encoding now called MIME Base64 was in the Privacy-Enhanced Mail (PEM) protocol, proposed by RFC 989 in 1987.[3]

In DuckDB, the BLOB type holds arbitrary binary data, and what the bytes represent is opaque to the database system.[5] DuckDB's encode function converts a string to a BLOB of its UTF-8 bytes, and decode converts a BLOB back to VARCHAR.[4] DuckDB's to_base64 converts a BLOB to a base64 string and from_base64 converts one back, while hex and unhex convert between a BLOB and hexadecimal.[4] UTF-8 is a character encoding standard defined by the Unicode Standard, and its name is derived from Unicode Transformation Format – 8-bit.[6]

DuckDB's BITSTRING type holds variable-length strings of 1s and 0s, needing 1 byte for each group of 8 bits plus a fixed amount of metadata.[7] Numeric values can be cast to a BITSTRING in DuckDB, and the bitstring function left-pads a bit string with zeroes to a given length.[7]

Under RFC 4180, Common Format and MIME Type for Comma-Separated Values (CSV) Files, fields containing line breaks, double quotes and commas should be enclosed in double quotes.[8] Under RFC 4180, a double quote inside a field enclosed in double quotes must be escaped by preceding it with another double quote.[8] Under RFC 8259, The JavaScript Object Notation (JSON) Data Interchange Format, the quotation mark, the reverse solidus and the control characters U+0000 to U+001F must be escaped inside a string.[9]

Sources

  1. [1]RFC 4648: The Base16, Base32, and Base64 Data Encodingsstandard
  2. [2]Mojibakesecondary source
  3. [3]Base64secondary source
  4. [4]Blob Functions – DuckDBdocumentation
  5. [5]Blob Type – DuckDBdocumentation
  6. [6]UTF-8secondary source
  7. [7]Bitstring Type – DuckDBdocumentation
  8. [8]RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard
  9. [9]RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Formatstandard