Mysql Bitwise operation

Question

I have a table so this table have one column which name is bit_column. And type of this column is varchar(64). this column only contain zeros and ones. (Ex: '1010100010011110111011101.....'). I want to XOR this column with given string (this string also contain zeros and ones). For ex: BIT_COUNT(b'01001' ^ b'01101') this return 1. So how can i do it on table column?

Storing binary data in a VARCHAR seems like a poor choice to me, and has given rise to the issue you are experiencing right now. Can you change the design to store it more apporpriately (and therefore allow bitwise operations)? — Martin
– Martin, Commented Oct 23, 2019 at 11:00

forpas · Accepted Answer · 2019-10-23 11:45:47Z

2

Convert the column value and the given string to decimal integer number with CONV() and CAST() and apply the bitwise XOR operator ^.
Then convert the result to binary and left pad it with zeroes :

SET @s = '0011001100110011001100110011001100110011001100110011001100110011';
SELECT
  LPAD(CONV(
    CAST(CONV(bit_column, 2, 10) AS UNSIGNED INTEGER) 
    ^ 
    CAST(CONV(@s, 2, 10) AS UNSIGNED INTEGER),
    10, 2
  ), 64, '0')
FROM tablename

See the demo.

answered Oct 23, 2019 at 11:45

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Mysql Bitwise operation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related