Search code examples
mysqlhexvarchar

Convert hex string to number in mySQL

I have a column that has the value '11B3' in it. I want to write a SQL statement (in mySQL) that ands (&) that value with 0x1880 and returns the result. I have been unable to treat the string column as a hex number. I would be grateful for any assistance.

This does NOT work:

select szVersion,  hex(szVersion), concat("0x",szVersion)

This functions as desired (but doesn't pull from database:

select 0x11bx & 0x1880
Solution

  • Use CONV(szVersion,16,10). Below is a proof that it works as expected.

    You wrote that you expect the same result as the result of the following statement:

    SELECT 0x11B3 & 0x1880;
-> 4224

    So we know that the desired result is the number 4224.

    Now do the same thing with data from a table:

    CREATE TEMPORARY TABLE temp_hex SELECT '11B3' AS szVersion;
SELECT CONV(szVersion,16,10) & 0x1880 FROM temp_hex;
-> 4224

    The same result. Works :)