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.

Solution 1

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

Solution 2

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.

Comments

[Tenakha]

tried it.. didn't quite work. I think the problem could be that CONCAT (setting1,setting2,setting3) - this gives us a STRING of 1's and 0's --- that need to be cast() to binary maybe?

[David Bélanger]

Your field is BINARY ? Because if you run this : SELECT CONV(HEX(UNHEX(6)), 16, 10), the result will be 6.

[Tenakha]

No the field is a string, because when you CONCAT all the individual 1's and 0's - you are left with a String.

[David Bélanger]

@Tenakha That's why. Use BINARY field to store data as BINARY. Event if it's only 110 or 111... Use BINARY(2) to store a string like that 1111 OR 0100 OR 0101...

[Payel Senapati]

best shortest precise answer