CODEWITHSUNDEEP
mysqlbinarydecimal
Tenakha
Tenakha

Reputation: 183

Convert a binary to decimal using MySQL

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.

Upvotes: 5

Views: 17232

Answers (4)

Niall
Niall

Reputation: 435

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

Upvotes: 4

Munaja
Munaja

Reputation: 149

This might be late, but in case someone got here we can combine several functions for the problem

The first function we can use CONV which allows us to convert input from some base to another base, the following sample convert 101, from base 2 (binary) to base 10 (decimal):

SELECT CONV(101, 2, 10)

in case the source is string, we can convert it to binary first using cast like:

SELECT CONV(CAST('11' AS BINARY), 2, 10)

Upvotes: 0

MoMo
MoMo

Reputation: 111

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.

Upvotes: 0

David Bélanger
David Bélanger

Reputation: 7438

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.

Upvotes: 1

Related Questions