HASH_DECODE

Decode binary data to a string using a named character set.

Category: hashReturns: STRINGDialect: Standard

Syntax

DECODE(expr, charset)

Description

## Overview Decodes binary data into a string by interpreting the bytes according to the named character set. Use this variant when data has been produced in a non-default character set and must be converted to the database's internal text form for comparison or display. This function is the inverse of ENCODE (the character-set form, HASH_ENCODE in this documentation). For the simpler three-format decoder (base64/hex/escape) see PG_DECODE. ## Behavior - Returns NULL if either argument is NULL. - Behavior on invalid byte sequences is engine-specific: some raise an error, some substitute a replacement character (typically U+FFFD). - Charset name is case-insensitive; common aliases are accepted. - Return type is STRING. ## Algorithm - Character-set decoding per the named charset's specification. ## Compatibility - Charset names follow the IANA character-sets registry. - Invalid-byte handling varies across engines; test with known-bad samples if strict behavior is required.

Parameters

Name	Type	Description
`expr`		Specifies the binary data to decode into a string using the given character set. Returns NULL for NULL input.
`charset`		Specifies the character set to decode with. Accepts standard names such as 'UTF-8', 'US-ASCII', 'ISO-8859-1', 'UTF-16'. The charset name is case-insensitive.

Examples

-- Round-trip through UTF-8
SELECT DECODE(ENCODE('hello', 'UTF-8'), 'UTF-8') AS t;

-- ASCII round-trip
SELECT DECODE(ENCODE('test', 'US-ASCII'), 'US-ASCII') AS t;

-- ISO-8859-1 round-trip
SELECT DECODE(ENCODE('data', 'ISO-8859-1'), 'ISO-8859-1') AS t;

-- Realistic: decode legacy payloads on read
SELECT message_id, DECODE(legacy_bytes, 'ISO-8859-1') AS body_text
FROM messaging.inbound.legacy_events;

Pitfalls

Invalid byte sequences are handled differently by different engines; some raise, some substitute U+FFFD. Validate behavior before relying on it.
UTF-16 input may include a BOM that causes the decoded string to begin with U+FEFF. Strip it when comparing to strings from other sources.
Not all engines support every IANA charset. Test each charset name before relying on it.
The DECODE name is shared with the base64/hex/escape decoder (PG_DECODE) and the Oracle-style conditional DECODE. Behavior depends on argument types and count; ensure callers know which variant is in use.
Misidentifying the source charset silently produces garbled output; the function cannot detect the correct encoding from bytes alone.