SQL: Get value at index in binary value

Question

Is there a SQL command that could be used in a query, stored procedure, function, that would work against a Binary Type similar to the following C# code?

if (someBinaryArray[index] == 0) { 
...

I'm wanting to check if an index of a position in the binary is a certain value instead of pulling the entire array down and doing the comparison?

Answer 1

You can use Substring(), according to the documentation it works with binary columns:

SELECT *
FROM Table
WHERE Substring(column, index, length) = 'blah'

If you really wanted to check for a null (as in your example)... you could do this:

SELECT *
FROM table
WHERE SUBSTRING(column, 3, 1) = CHAR(0)

Answer 2

If you work on MSSQL Server, you can use the READTEXT command

CREATE TABLE #t (b varbinary(1))

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(mybinarraycolumn) 
FROM mytable WHERE pk = @pk

INSERT INTO #t (b)
READTEXT pub_info.pr_info @ptrval @index 1

DECLARE @b varbinary(1)
SELECT @b = b FROM #t