In DB2 the HEX scalar function converts a value into its hexadecimal representation - HEX(col) returns a string of hex digits for whatever you pass it - but the reverse, turning a hex string back into readable characters, is the part people actually get stuck on, because DB2 has no single clean built-in for hex-to-string and the right approach depends on your DB2 version and the data’s encoding. Here is both directions, and the caveats that bite.
Half the searches for this want HEX going out; the other half want it coming back in, which is the harder and less documented direction. This covers both.
Table of contents
- String to hex with HEX()
- Hex back to characters
- Encoding is the real trap
- The portable mindset for migrations
- Where RunxBuild fits
- FAQ
String to hex with HEX()
The easy direction. HEX() takes a value and returns its hexadecimal representation as a string of hex digits - two characters per byte. For a character column:
SELECT HEX(name) FROM employees;If name contains ABC, you get 414243 (the hex of A, B, C in ASCII/EBCDIC depending on your system encoding). This is useful for inspecting exactly what bytes are stored - whitespace, control characters, or encoding surprises become visible as hex when they were invisible as text.
That is the function the IBM docs describe, and it is stable across DB2 versions. The complication is always the other way.
Hex back to characters
There is no universally clean single built-in that reverses HEX in every DB2 version, which is why this question fills forums. The common approaches:
On newer DB2, you can often use a combination with HEXTORAW-style casting or the built-in functions that interpret a hex string, depending on platform (DB2 for z/OS, LUW, and i differ here). A frequently used portable technique is to cast the hex string to a binary/CHAR FOR BIT DATA type and then to the target character type.
Because the exact function name and availability vary by platform and version, the reliable move is to check your specific DB2 edition’s documentation for its hex-decoding function rather than copying a snippet written for a different edition - a snippet for z/OS may not exist on LUW.
Encoding is the real trap
The subtlety that causes wrong results is encoding. Hex is just bytes; turning bytes back into characters requires knowing the character set those bytes represent. DB2 systems can use EBCDIC or ASCII/UTF-8 depending on the platform, so the same hex can decode to different characters on different systems.
If your hex came from one system and you decode it on another with a different encoding, you get garbage even though the SQL is correct. Always know the encoding of the source bytes, and make sure your decode interprets them the same way. This is why a hex-to-string that works on one DB2 instance can produce nonsense on another - the SQL is fine, the encoding assumption is not.
The portable mindset for migrations
If you are converting hex because you are moving data between systems - a common reason this comes up - resist baking DB2-specific hex functions deep into application logic. Other databases handle hex differently: SQL Server uses CONVERT with style codes, Postgres uses encode/decode with a hex format, MySQL uses HEX and UNHEX.
The portable approach is to do the hex handling in a thin, clearly marked layer, or in application code where you control the encoding explicitly, so swapping databases does not mean hunting hex conversions through the whole codebase. The more your conversions live in one place, the less a migration hurts.
Where RunxBuild fits
RunxBuild offers managed MySQL and Postgres, not DB2, so if you are converting DB2 hex as part of a migration, the destination side is where we fit: on Postgres the equivalents are encode(col, ‘hex’) and decode(hexstr, ‘hex’), and on MySQL they are HEX() and UNHEX() - cleaner round-trips than DB2 offers in either direction.
That makes a hex-heavy DB2 migration simpler on the target: the decode direction that is awkward in DB2 is a single built-in in Postgres and MySQL. You deploy the app and its managed database from a GitHub repo, and the conversion logic that was scattered across DB2-specific functions collapses to the standard encode/decode pair.
How this fits the rest of the stack
The cost of a project is the sum of its line items, not the headline number, and the RunxBuild hosting calculator is the right place to add them up: the service, the database, the storage, the bandwidth, each a separate figure. Model it before committing, not after the first invoice. The RunxBuild dashboard is where the running project shows its real usage in one place.
Useful related references:
FAQ
How do I convert a string to hex in DB2?
Use the HEX scalar function: SELECT HEX(column) returns the hexadecimal representation as a string of hex digits, two characters per byte. It is stable across DB2 versions and useful for inspecting the exact bytes stored, including whitespace or control characters invisible as plain text.
How do I convert hex back to a string in DB2?
There is no single universal built-in that reverses HEX in every DB2 version. Common approaches cast the hex string to a binary or CHAR FOR BIT DATA type and then to the target character type, but the exact function varies by platform (z/OS, LUW, i), so check your specific edition’s documentation.
Why does my DB2 hex conversion produce garbage?
Almost always an encoding mismatch. Hex is just bytes, and decoding them to characters requires knowing the character set - EBCDIC, ASCII, or UTF-8 - they represent. Hex produced on one system and decoded on another with a different encoding yields nonsense even when the SQL is correct.
Is hex conversion the same across databases?
No. DB2 uses HEX and awkward reverse handling, SQL Server uses CONVERT with style codes, Postgres uses encode and decode with a hex format, and MySQL uses HEX and UNHEX. Keeping conversions in one thin layer makes migrating between them far less painful.
What are the Postgres and MySQL equivalents of DB2 HEX?
In Postgres, encode(column, ‘hex’) and decode(hexstring, ‘hex’) handle both directions cleanly. In MySQL, HEX() and UNHEX() do the same. Both offer a simpler round-trip than DB2, where the decode direction lacks a single clean built-in.