MySQL in a Nutshell, 2nd Edition

Name

HEX()

Synopsis

HEX(string)

The first version of this function accepts a string and returns its numerical value, in hexadecimal, as it is represented in the underlying character set. The second version accepts a decimal integer and returns the hexadecimal equivalent. The function returns NULL if given a NULL value.

For an example, suppose that a college has conducted a student survey through an application that has somehow saved a number of formatting characters as strings containing their hexadecimal equivalents. For instance, a tab appears as 09, and we want to replace each instance with an actual tab. Although we could do this with a straight replacement function, we’d like to use a slightly more abstract solution that can be used with many different characters that suffer from this problem in a particular column.

One solution, changing all instances in the column student_surveys, is as follows:

UPDATE student_surveys
SET opinion = REPLACE(opinion, HEX('\t'), UNHEX(HEX('\t')))
WHERE survey_id = 127;

In this SQL statement, HEX() is used to return the hexadecimal value of tab, represented by \t. That value is given to REPLACE() as the string for which it is to replace. Then, using HEX() again but wrapped in UNHEX() to return the binary character for tab, we’re providing REPLACE() with the replacement value.