14

Given a binary(16) column, how do I display its value as a hexadecimal number? I've been experimenting in the console a little below, and I'm not getting the results I expect. Could it be that I'm not converting numbers to binary properly?

I have the following sample query: select hex(cast(10 as binary)),

but the output is rather unexpected: 3130.

Basically, it appears to just take the decimal representation of the number, and insert a 3 digit before each digit, e.g. 2 -> 32, 22 -> 3232, 678 -> 363738, etc.

If I specify a length for the binary data type (e.g. binary(16)), I get the same behavior, except that it gets padded on the right with the required number of 0s.

Obviously, that's not what I'm looking for. What am I missing?

EDIT: just tried convert(678, binary) instead of cast, same behavior.

gzak
  • 3,908
  • 6
  • 33
  • 56
  • 2
    please be clearer about exactly what input and base you're using, and what output you're expecting – Alnitak Dec 05 '16 at 17:59
  • 1
    `HEX()` represents the hexadecimal representation of the byte values used to store that data. `3130` represents the ASCII characters 49 and 48, or in other words `1` and `0`. – tadman Dec 05 '16 at 18:14
  • @Alnitak, the title of the post is clearer, the sample query is me just experimenting with binary data types in the console to try to get it to print the expected hex value. – gzak Dec 05 '16 at 18:18
  • No, that's completely unclear, because `binary(16)` makes no sense - 16 isn't a binary number. – Alnitak Dec 05 '16 at 18:53
  • 1
    OK, I now understand - you have a `binary(16)` column, which contains some data. Just doing `SELECT hex(colname) FROM table` should work fine. If it doesn't, please elaborate, and stop getting bogged down on using numeric literals in the console. – Alnitak Dec 05 '16 at 21:11
  • If on the other hand you really need to know how to convert a number into its binary blob representation (and then perhaps into hex), that's another question altogether. – Alnitak Dec 05 '16 at 21:12
  • I've voted to close the question because it isn't clear what is being asked (and is old, with no accepted answer). OP appears to be confused about the meaning and usage of `binary` type. From his last sentence, I suspect he wanted to convert a decimal value to *hexadecimal*, and then store it in `binary`. But who knows? And what is his *real* goal? Maybe it isn't a *database type* problem at all, but a *display format* problem? [In that case, could simply store it as an integer, then read it back, and display as desired, using string formatting in his chosen programming language.] – ToolmakerSteve Apr 09 '19 at 19:42

4 Answers4

32

Why make things complicated? I just use this to display the value of a BINARY(16) column:

SELECT HEX(colname);

Blue
  • 22,608
  • 7
  • 62
  • 92
lfjeff
  • 1,840
  • 2
  • 17
  • 19
4

You can also set your mysql client to display binary data as hex, as described here:

Does the mysql CLI tool provide a way to display binary data in a console-friendly manner?

mysql --binary-as-hex

or

[client]
binary-as-hex = true
Alex
  • 32,506
  • 16
  • 106
  • 171
3

Use CONV function

-- convert '10000'(16 in binary) in hex
SELECT CONV('10000',2,16);
-- Ouput: 10


-- convert 'F' in hex to binary
SELECT CONV('F',16,2);
-- Output: 1111

Hexadecimal to binary table

Hexadecimal Binary
0         0000
1         0001
2         0010
3         0011
4         0100
5         0101
6         0110
7         0111
8         1000
9         1001
A         1010
B         1011
C         1100
D         1101
E         1110
F         1111
10       10000
11       10001
...
Antonio Alexandre
  • 276
  • 2
  • 6
0

Just try this and you will clear your mind:

select conv(cast(10 as binary), 10, 16);

BINARY data type is treated as decimal string by default

Jairo Andres Velasco Romero
  • 1,994
  • 1
  • 19
  • 31