How to get individual bytes from a SQL binary field

Question

I have a binary field in SQL Server which I want to read one byte at time in a SQL function. In code I would use a byte array. Is there an equivalent in SQL?

I couldn't find anything with google.

Answer 1

Varbinary as a type will act as a byte array, and you can read an individual byte from it using substring.

Answer 2

The SUBSTRING function should be sufficient. A quick example, assuming table MyTable with column SomeData, binary(10) not null:

DECLARE
  @OneByte  binary(1)
 ,@Loop     int


SET @Loop = 0
WHILE @Loop < 10
 BEGIN
    SET @Loop = @Loop + 1

    SELECT @OneByte = substring(SomeData, @Loop, 1)
     from MyTable

    --  Process accordingly
 END

There are fancier set-based ways to do this, but for short values this should be adequate.

Answer 3

You could loop through the binary field using SUBSTRING.

declare @BinaryColumn binary(5)

set @BinaryColumn = convert(binary,'abcde')

declare @Counter int, @ColumnLength int
set @Counter = 1
set @ColumnLength = LEN(@BinaryColumn)

while (@Counter <= @ColumnLength) begin
    select SUBSTRING(@BinaryColumn, @Counter, 1)
    set @Counter = @Counter + 1
end /* while */