MySQL - how do I unhex a field rather than a string?

Question

I've got a table which contains a varchar(1024) field, which in that contains strings which has hex encoded strings. This table is filled automatically and I have to provide an SP to allow users to download this, therefore, I need to change the hex back into human readable form.

If I manually run this statement (taking the Hex data from the field), it works just fine:

SELECT X'5468697320697320612074657374206D6573736167652031323334353637383930';

But I cannot find a working example of getting this to work when calling the field/column name. I've found a few examples, but these just return a null or 0.

I've tried X and UnHex() and neither give me a result.

Where am I going wrong?

Thanks

EDIT:

Okay, after doing a bit more testing, it appears it must be the way it's being written to the database in the first place.

It's a Classic ASP page that calls an SP, which creates the database entry. In this method, the write to the DB works, and I can see the HEX content in the field. Copying the content of the field, and putting this into a Select X'123123' gives me the ASCII values, as I want.

If I try this as a Select, this fails, giving me a zero or Null return.

SELECT Message_Body_Hex, UNHEX(Message_Body_Hex) FROM messages_inbound

returns:

Message_Body_Hex......unhex(Message_Body_Hex)

417265612032........(NULL)

Still confused! :)

Mikael S · Answer 1 · 2013-03-18T14:25:23.193

1

Using the UNHEX() function seems to work fine on MySQL 5.5.29-1:

mysql> create table t1 ( f1 varchar(1024) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('5468697320697320612074657374206D6573736167652031323334353637383930');
Query OK, 1 row affected (0.02 sec)

mysql> select f1 from t1; 
+--------------------------------------------------------------------+
| f1                                                                 |   
+--------------------------------------------------------------------+
| 5468697320697320612074657374206D6573736167652031323334353637383930 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select unhex(f1) from t1; 
+-----------------------------------+
| unhex(f1)                         |   
+-----------------------------------+
| This is a test message 1234567890 |
+-----------------------------------+
1 row in set (0.00 sec)

edited Mar 18 '13 at 14:25

answered Mar 18 '13 at 13:17

Mikael S

5,206
2
23
21

Thanks for trying that. I have just replicated what you created and it worked on my server too: But when I change the f1 and t1 to my tables, I just get NULL returned. I'm confused. I'm running 5.5.18, but as your example works, I am unsure as to what the issue is. The only difference is the table names but I can't see that causing it. – SparkyUK2104 Mar 18 '13 at 13:57
Changed it to reflect what table/field names I have, but just get NULL returned: SELECT UNHEX(Message_Body_Hex) FROM instructions_inbound; – SparkyUK2104 Mar 18 '13 at 13:58
Can you show us what gets returned when you `SELECT Message_Body_Hex FROM instructions_inbound`. Maybe the data is not being stored the way you expect. – ckim Mar 18 '13 at 17:17
I just get (NULL) returned, although I can see the content if I don't UnHex() it in the Select. – SparkyUK2104 Mar 20 '13 at 13:04

score 1 · Accepted Answer · edited May 23 '17 at 12:31

I realize this is an old question but I ran into this same problem today and solved it using a combination of HEX and CAST. Using your example, try this:
SELECT HEX(CAST(X'5468697320697320612074657374206D6573736167652031323334353637383930' AS CHAR(33)))

When pulling from a table you'd substitute the field name:

SELECT HEX(CAST(binary_field AS CHAR(33)))

I've seen other answers recommending to use MAX in place of the 33 but this appears to work fine. Here are some sources I used:
SQL Server converting varbinary to string
and
How to convert from varbinary to char/varchar in mysql

MySQL - how do I unhex a field rather than a string?

2 Answers2