Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL HEX() and UNHEX() functions

Reference for HEX() and UNHEX(): convert text and binary values to hexadecimal and back, SQLite behavior, blob literals and the PostgreSQL, MySQL and SQL Server forms.

HEX(x) turns a value into a text string of hexadecimal digits, two digits for every byte. UNHEX(h) does the reverse and turns a hexadecimal string back into binary data. Hexadecimal is how binary values (hashes, keys, random bytes, file fragments) are shown and typed, because raw bytes are unreadable.

In SQLite, HEX() first looks at the bytes of its argument. For text it uses the UTF-8 bytes; for a number it first converts the number to text. That last point surprises people who know HEX(255) as FF in MySQL.

SQL
SELECT HEX('abc');      -- 616263
SELECT HEX(x'FF00');    -- FF00

Try it

SQL From text and blobs to hex

Press Run (or Ctrl + Enter) to see the result here.

SQL Back from hex with UNHEX()

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
HEX(x)
UNHEX(hex_string)
UNHEX(hex_string, ignore_chars)
Name Description
x A blob, text or number. Its bytes are written as uppercase hex digits.
hex_string Text made of the digits 0-9, a-f, A-F, with an even number of digits.
ignore_chars Optional (SQLite). Characters allowed between byte pairs, such as spaces or dashes, that are skipped.

Returns: HEX() returns uppercase text (an empty string for NULL); UNHEX() returns a blob, or NULL if the input is not valid hexadecimal or is NULL.

Dialect differences

Database Hex of data Hex back to data
SQLite HEX(x) (bytes of the value; numbers become text first) UNHEX(h) since SQLite 3.41
PostgreSQL encode(bytea, 'hex') (lowercase) decode(text, 'hex')
MySQL HEX(x); a number is converted to base 16, so HEX(255) is 'FF' UNHEX(h)
SQL Server CONVERT(varchar(max), varbinary_value, 2) CONVERT(varbinary(max), 'hex', 2)
Oracle RAWTOHEX(raw) HEXTORAW(text)
SQL
-- PostgreSQL
SELECT encode('abc'::bytea, 'hex');           -- 616263
SELECT convert_from(decode('616263', 'hex'), 'UTF8');   -- abc

-- MySQL
SELECT HEX('abc'), HEX(255), UNHEX('616263');   -- 616263, FF, abc

Usage notes

  • Two digits per byte. A 16-byte value (a UUID, an MD5 hash) becomes a 32-character string.
  • Case. SQLite prints uppercase digits. Compare hex strings after UPPER() or LOWER() when other systems produce a different case.
  • Blob literals. x'2a' (or X'2A') writes a blob directly in SQLite and MySQL; PostgreSQL uses '\x2a'::bytea. Use QUOTE() to print a blob back as a literal.
  • To get the number 255 in hex, SQLite has no built-in; use printf('%X', 255) (printf()).
  • Showing blobs. Query tools often cannot display raw binary, so wrap blob columns in HEX().
  • Invalid input to UNHEX() returns NULL in SQLite rather than raising an error.