Convert hexadecimal text to regular string in MySQL?

Question

How do I convert a hexadecimal text to a regular readable (varchar) string in MySQL?

Ive seen some functions to convert hex to numbers but nothing to string.

Try answer from this question: http://stackoverflow.com/questions/8339138/how-to-convert-a-string-to-hex-and-vice-versa — Ravi Dhoriya ツ, May 09 '14 at 08:04
BTW, that's asking for the MSSQL version. This is for MySQL. — riahc3, May 09 '14 at 08:08

riahc3 · Accepted Answer · 2014-05-09T08:10:36.730

6

Answer is:

select CAST(unhex('hexstringhere') AS CHAR(100))

Figured it out via trial and error.

edited May 09 '14 at 08:10

answered May 09 '14 at 08:05

riahc3

857
4
10
23

S. Moreno · Answer 2 · 2014-05-09T08:15:26.843

Use the function UNHEX

For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT 0x4D7953514C;
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'

Font: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_unhex

Convert hexadecimal text to regular string in MySQL?

2 Answers2