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.
CONV(BINARY(CONCAT(setting1, setting2)), 2, 10)
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.
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.
