I have the following data in a table like so:
create table tutor( id int, accessto varchar(8) );
The data looks like this:
+-------+----------+ | id | accessto | +-------+----------+ | 69 | b'1011' | | 162 | b'1011' | | 232 | b'1011' | | 257 | b'0010' | | 258 | b'1011' | | 258 | b'1011' | | 258 | b'1011' | | 258 | b'1011' | | 258 | b'1011' | | 258 | b'1011' | | 258 | b'1110' | | 258 | b'1001' | | 258 | b'0011' | | 258 | b'1001' | +-------+----------+
I want to convert this into binary. How is it possible?
I tried these queries but getting the same result:
select id, cast(accessto as BINARY) from tutor; select id, convert(accessto,binary) from tutor;
How to do this?
My expected result is should be like this:
+-------+----------+ | id | accessto | +-------+----------+ | 69 | 11 | | 162 | 11 | | 232 | 11 | | 257 | 2 | | 258 | 11 | | 258 | 11 | | 258 | 11 | | 258 | 11 | | 258 | 11 | | 258 | 11 | | 258 | 14 | | 258 | 9 | | 258 | 3 | | 258 | 9 | +-------+----------+
Advertisement
Answer
Since the accepted answer is unnecessarily complex, here a concise answer:
TLDR;
The result can be achieved in one step:
SELECT CONV(TRIM("'" FROM SUBSTRING("0b'1011'", 3)), 2, 16);
Explanation
Starting point is a string containing 0b1011
:
SET @string = "0b'1011'"; -- (string) 0b'1011'
We can apply string operations to get it closer to a number:
SET @plainnumber = TRIM("'" FROM SUBSTRING(@string, 3)); -- (string) 1011
We can then convert the string into a real number, without sign because there is none: (You cannot use binary as a type here, because it is a string type. This step is optional because MySQL will cast implicitly.)
SET @number = CAST(@plainnumber AS UNSIGNED); -- (unsigned) 1011
You can now do whatever you want with it. The OP wanted to get the decimal representation which can be acquired with CONV:
SELECT CONV(@number, 2, 10);
Explanation for hexadecimal
If your starting point is a string containing a hexadecimal representation:
SET @string = "41"; -- 0x41
You can use the UNHEX function to get a binary string:
SELECT UNHEX(@string);
Or you can use CONV to get a numerical representation:
SELECT CONV(@string, 16, 10); -- decimal (65) SELECT CONV(@string, 16, 2); -- binary (0100 0001)