Name
UNHEX()
Synopsis
UNHEX(string
)
This function converts hexadecimal numbers to their character equivalents. It reverses the results of the HEX() function and is available as of version 4.1.2 of MySQL.
To illustrate its use, suppose that in a table we have a column
with a binary character in the data; specifically, tabs were entered
through a web interface using an API. However, the column is a
VARCHAR
data type. The problem is that when the
data is retrieved, we want to line up all the results in our display
by counting the length of each column, and a tab keeps the display
from lining up vertically. So we want to fix the data. We can use
UNHEX()
to locate rows containing the binary
character and then replace it with spaces instead:
UPDATE students SET comments = REPLACE(comments, UNHEX(09), SPACE(4)) WHERE LOCATE(UNHEX(09), comments);
We’ve looked at an ASCII chart and seen that a tab is
represented by the hexadecimal number 09
. Knowing
that bit of information, in the WHERE
clause we’re
passing that value to UNHEX()
to return the
binary character for a tab, yielding the search string with which
LOCATE()
will search the column
comments
. If it doesn’t find a tab in the column
for a row, it will return 0. Those rows will not be included in the
search results. The ones that do contain tabs will have a value of 1
or greater and therefore will be included in the results. Using
UNHEX()
along with
REPLACE()
in the SET
clause, we replace all tabs found with four spaces.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.