MySQL HEX()

The HEX() function in MySQL takes an input and returns its equivalent hexadecimal representation. This input can be either a string or a numeric value.

When the input is a string, the resulting hexadecimal representation will, by default, be a binary string. In the output, each byte of each character in the original string argument is represented by a corresponding pair of hexadecimal digits. The resulting hexadecimal string can also be converted back into the original string by using the UNHEX() function.

When the input is a decimal number, HEX(N) and CONV(N, 10, 16) produce the same hexadecimal output. The hexadecimal result from HEX(N) can be converted back to the original number using CONV(HEX(N), 16, 10).

Syntax

HEX(val);

• val: The value to be represented as a hexadecimal string.

Examples

The following example uses the HEX() function to convert the codecademy string to hexadecimal:

SELECT HEX('codecademy');

Copy to clipboard
Copy to clipboard

The result looks like this:

The next example passes a numeric value instead of a string to the function:

SELECT HEX(123456789);

Copy to clipboard
Copy to clipboard

The resulting output is the following:

Below is a more detailed example, where the HEX() function is used with a column in a table:

-- Create a table named 'employees'
CREATE TABLE employees (
  last_name VARCHAR(50),
  employee_id INT
);

-- Insert data
INSERT INTO employees (last_name, employee_id) VALUES
('Smith', 40388),
('Jones', 80666),
('Brown', 64695),
('Miller', 23088),
('Williams', 15312);

-- Verify that the data is inserted correctly
SELECT * FROM employees;

Copy to clipboard
Copy to clipboard

The resulting table looks like this:

Now, the HEX() function is used to retrieve the data in the last_name column as hexadecimal strings:

SELECT last_name, HEX(last_name) FROM employees;

Copy to clipboard
Copy to clipboard

Here is the expected output: