how to update flag bit in mysql query?

Question

This is my sql query,In flag(00000) every bit position have different specification, e.g. change 4th bit position to 1 when user is inactive.Here flag is varchar datatype(String).

$sql="select flag from user where id =1"

I got

flag=10001 #it may be flag="00001" or flag="00101"

I want to update 2nd bit of this flag to 1.

$sql="update user set flag='-1---' where id=1" #it may be flag='11001' or flag='01001' or flag='01110'

Actually,I want to to update 2nd bit of this flag to 1,but with out updating it like flag='11001'.I want to do some thing like this.

$sql="update user set flag='--change(flag,2bit,to1)--' where id =1" #this is wrong

What can I do for it , only using one sql query?Is it possible?

Answer 1

update user
set flag = lpad(conv((conv(flag, 2, 10) | 1 << 3), 10, 2), 5, '0')
where id = 1

• conv(flag, 2, 10) converts the flag string from binary to decimal.
• 1 << 3 shifts a 1 bit 3 binary places to the left
• | performs a binary OR of this, to set that bit. This arithmetic operation will automatically coerce the decimal string to a number; you can use an explicit CAST if you prefer.
• conv(..., 10, 2) will convert the decimal string back to a binary string
• lpad(..., 5, '0') adds leading zeroes to make the string 5 characters long

FIDDLE DEMO

To set the bit to 0, you use:

set flag = lpad(conv((conv(flag, 2, 10) & ~(1 << 3)), 10, 2), 5, '0')