converting varchar to binary in mysql?

Question

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  |
    +-------+----------+

What's your expect result?

D-Shih
– D-Shih

2018-09-03 07:25:26 +00:00
Commented Sep 3, 2018 at 7:25 — D-Shih
– D-Shih, Commented Sep 3, 2018 at 7:25

Ulrich Thomas Gabor · Accepted Answer · 2021-03-03 09:18:45Z

2

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)

edited Mar 3, 2021 at 9:18

answered Mar 3, 2021 at 9:13

Ulrich Thomas Gabor

6,6844 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ulrich Thomas Gabor Over a year ago

See also this answer if you are converting UUIDs

P.Salmon Over a year ago

There should be some sort of bonus for excavating and providing a solution to such an old question..

Ulrich Thomas Gabor Over a year ago

@P.Salmon Actually, there is: stackoverflow.com/help/badges/17/necromancer?userid=3340665

P.Salmon · Accepted Answer · 2018-09-03 08:09:19Z

1

You may have to do this the 'hard' way and create a bit field either in tutor or another table, internal conversion seems to work better than cast and convert

drop table if exists t;
create table t(
   id int,
   accessto varchar(8),
   abit bit(8)
);


insert into t(id,accessto) values
(    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') ;

update t
set abit = accessto;

    MariaDB [sandbox]> select bin(abit),cast(abit  as unsigned) from t;
+-----------+-------------------------+
| bin(abit) | cast(abit  as unsigned) |
+-----------+-------------------------+
| 1011      |                      11 |
| 1011      |                      11 |
| 1011      |                      11 |
| 10        |                       2 |
| 1011      |                      11 |
| 1011      |                      11 |
| 1011      |                      11 |
| 1011      |                      11 |
| 1011      |                      11 |
| 1011      |                      11 |
| 1110      |                      14 |
| 1001      |                       9 |
| 11        |                       3 |
| 1001      |                       9 |
+-----------+-------------------------+
14 rows in set (0.00 sec)

answered Sep 3, 2018 at 8:09

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

2 Comments

Karan Gupta Over a year ago

yes absolutely. Since "b'1011'" is already in bit notation for insertion. Thanks.

Ulrich Thomas Gabor Over a year ago

This can be done without an additional table, see my answer.

kellymandem · Accepted Answer · 2018-09-03 07:34:08Z

-1

You should using CONV mysql function

select id, CONV(BINARY(accessto), 2, 10) from tutor;

answered Sep 3, 2018 at 7:34

kellymandem

1,7693 gold badges18 silver badges29 bronze badges

Comments

Jan de Nysschen · Accepted Answer · 2018-09-03 07:49:09Z

-1

-- Replace 'binary' with 'UNSIGNED'

select id, cast(accessto as UNSIGNED) from tutor;

edited Sep 3, 2018 at 7:49

answered Sep 3, 2018 at 7:44

Jan de Nysschen

1277 bronze badges

1 Comment

Karan Gupta Over a year ago

All converted to 0 :(

Collectives™ on Stack Overflow

converting varchar to binary in mysql?

4 Answers 4

TLDR;

Explanation

Explanation for hexadecimal

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

TLDR;

Explanation

Explanation for hexadecimal

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related