More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How do I manipulate varbinary columns in Sybase ?
drop table demo_table
drop procedure clear_bits
drop procedure set_bits
go
create table demo_table (id numeric(18,0) identity, binary_col
binary(20))
go
insert demo_table values (0xffffffffffffffffffffffffffffffffffffffff)
insert demo_table values (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa)
insert demo_table values (0x0000000000000000000000000000000000000000)
go
create procedure clear_bits (
@id numeric(18,0), -- primary key of row to be changed
@bytenum tinyint, -- specifies which byte of binary_col to change
@mask binary(1) -- bits to be cleared are zeroed,
-- bits left alone are turned on
-- so 0xff = clear all, 0xfb = clear bit 3
)
as
update demo_table set binary_col =
substring(binary_col,1,@bytenum-1)+
convert(binary(1),
convert(tinyint,substring(binary_col,@bytenum,1)) &
convert(tinyint,@mask)
)+
substring(binary_col,@bytenum+1,20)
from demo_table
where id = @id
go
create procedure set_bits (
@id numeric(18,0), -- primary key of row to be changed
@bytenum tinyint, -- specifies which byte of binary_col to change
@mask binary(1)) -- bits to be set are turned on
-- bits left alone are zeroed
-- so 0xff = set all, 0xfb = set all but 3
)
as
update demo_table set binary_col =
substring(binary_col,1,@bytenum-1)+
convert(binary(1),
convert(tinyint,substring(binary_col,@bytenum, 1)) |
convert(tinyint,@mask)
)+
substring(binary_col,@bytenum+1,20)
from demo_table
where id = @id
go
select * from demo_table
-- clear bits 2,4,6,8 of byte 1 of row 1
exec clear_bits 1,1,0xAA
-- set bits 1-8 of byte 20 of row 3
exec set_bits 3,20,0xff
-- clear bits 1-8 of byte 4 of row 2
exec clear_bits 2,4,0xff
-- clear bit 3 of byte 5 of row 2
exec clear_bits 2,5,0x08
exec clear_bits 2,6,0x0f
exec set_bits 2,10,0xff
go
select * from demo_table
go
(Continued on next question...)
