The first version of this MySQL function accepts a string and returns its numerical value, in hexadecimal, as it is represented in the underlying character set.
Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).
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 retursn 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 could 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(' '), UNHEX(HEX(' '))) WHERE survey_id = 127;
In this SQL statement, HEX( ) is used to return the hexadecimal value of tab, represented by . 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.