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)