Changing a single bit in a 16 bit integer within in a table

1

I'm a beginner and need to use SQL as its been interfaced with our controls system onsite.
I have been able to select and test the single bit using the SELECT FROM Table WHERE the (Integer & POWER (2,3)) = 8 which is the bit I'm interested in but I'm finding it difficult to just change that bit without affecting the other bits. If I can call the status of that bit using the POWER Function why cant I simply UPDATE or SET it using the same POWER function without all the issues I'm encountering:(
I've also successfully used the Bitwise exclusive ^ Function to change it but it affects the other bits if they are also high!!!

2

Just use normal bitwise logic.
To flip a bit in an integer use bitwise exclusive or - ^
To set a bit regardless of its current value use bitwise or - |

The following updates bit 4 - the number 8:

-- Create test number table
SELECT number, number AS AlteredNumber
INTO #t
FROM [master].dbo.spt_values
WHERE [type] = 'P';

select * from #t order by number;

-- Flip bit 4
UPDATE #t
SET AlteredNumber ^= 8;

select * from #t order by number;

-- Set bit 4 regardless of its current value
UPDATE #t
SET AlteredNumber |= 8;

select * from #t order by number;
1 Like
3

Thank you so much, I 'll try this as soon as I can:)

4

That worked....thank you so much:)