Convert a binary to decimal using MySQL

Question

I am trying to construct a query in MySQL that concatenates a bunch of binary fields and then gives me the result as DECIMAL.

e.g:

SELECT CONCAT (setting1, setting2, setting3) AS settings;

may gave me:

101
111
110

I want to pass this value to a function (convert? cast?) that will give me the corresponding DECIMAL value:

5
7
6

I've already tried a few combinations of cast() and convert() but haven't cracked it yet.

Answer 1

You could try doing power math in binary

SELECT setting1 * 4 + setting2 * 2 + setting3 * 1 AS settings;

but what it means it is doing

SELECT setting1 * POW(2, 2) + setting2 * POW(2, 1) + setting3 * POW(2, 0) AS settings;

2^0 represents the right-most bit

2^1 is the second, and so on...

In the future if you have binary columns in MySQL, it could be easier to combine them into one [leave a comment on the column to remember the order]

You would add data into the database like

INSERT INTO settings (setting) VALUES (setting1 * 4 + setting2 * 2 + setting1 * 1);

and pull a single one out with SELECT setting & 2 FROM settings; will return the value of setting2.

Look into Bitwise Functions.

Answer 2

CONV(BINARY(CONCAT(setting1, setting2)), 2, 10)

Answer 3

Didn't try, but try it.

First, your input let's say is 6.

INSERT INTO Table (FieldTest) VALUES (UNHEX(CONV(6, 10, 16)))
SELECT CONV(HEX(UNHEX(6)), 16, 10)

with a field

SELECT CONV(HEX(FieldTest), 16, 10))

UNHEX transform data from hex to binary.

CONV will transform data from one base to another.

Here to transform base 10 to 16 (dec to hex) and then we do hex to bin.

When come the select, we do BIN to HEX, HEX to BIN

In reality, you could just do CONV(6, 10, 2) and the reverse when reading.