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

Question

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 1

You could convert BIT to VARCHAR (or CHAR) and then use SUBSTR in following:

SELECT SUBSTR(CONVERT(VARCHAR(10),numb), 3, 1) 
FROM numbers

Or using LEFT and RIGHT:

SELECT LEFT(RIGHT(CONVERT(VARCHAR(10),numb),8),1)
FROM numbers

Answer 2

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.

Answer 3

If you have a bit column, then use bit operations.

These are documented here.

One method is:

select ( (numb & b'0010000000') > 0)