stack - 2 years ago 67
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?

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`.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download