Sql – How to get individual bytes from a SQL binary field

sql, sql-server

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.

Best Solution

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

DECLARE  @OneByte  binary(1) ,@Loop     intSET @Loop = 0WHILE @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.