stack stack - 7 months ago 10
SQL Question

How to use substr(...) for BIT(...) data type columns?

I have this table:

// numbers
+---------+------------+
| id | numb |
+---------+------------+
| int(11) | bit(10) |
+---------+------------+
| 1 | 1001100111 |
| 2 | 0111000101 |
| 3 | 0001101010 |
| 4 | 1111111011 |
+---------+------------+


Now I'm trying to get third digit (left to right) from those number. Example:

1001100111
^ // I want to get 0


So it is expected result:

+--------------------+
| substr(numb, 3, 1) |
+--------------------+
| 0 |
| 1 |
| 0 |
| 1 |
+--------------------+





Here is my query:

SELECT SUBSTR(numb, 3, 1) FROM numbers


But it doesn't work. because
bit(10)
isn't string and
SUBSTR()
cannot parse it. Is there any workaround?

Answer

Although you could use substr after converting to varchar, a simpler approach for BIT(...) data type it to use bit operators.

Since according to your comment it is OK to extract 8-th bit from the right, rather than the third bit from the left, this will produce the expected result:

select id, (x>>7)&1
from test

Demo.

Is it possible to I update just one of its digits? I mean I want to update seventh digit (right to left) from 1001011101 and make it 0?

You can set a single bit to zero like this:

UPDATE test SET x = x & b'1110111111' WHERE id=3

Position of 0 indicates the bit you are setting to zero.

If you want to set it to 1, use

UPDATE test SET x = x | b'0001000000' WHERE id=3

You can have more than one zero in the first example if you would like to set multiple bits to zero. Similarly, you can have more than one 1 in the second example if you need to set multiple bits to 1.