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.
SELECT HEX('abc'); -- 616263 SELECT HEX(x'FF00'); -- FF00
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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) |
-- 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()orLOWER()when other systems produce a different case. - Blob literals.
x'2a'(orX'2A') writes a blob directly in SQLite and MySQL; PostgreSQL uses'\x2a'::bytea. UseQUOTE()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()returnsNULLin SQLite rather than raising an error.