Varbinary update attempt

Is it possible to adapt this into update query which has to seek a free sequence of 3 bytes or (0x000000) and replace it with a new sequence if the data is split like this every 3 bytes? The issue is that cast/replace wont work with undefined data which is 0x00.
The varbinary data is divided every 3bytes -> data(3bytes long)data(3bytes long).

DECLARE @YourTable table
(
Id INT PRIMARY KEY,
Val VARBINARY(50)
)

INSERT @YourTable
VALUES (1, 0x0329000414000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000),
       (2, 0x0329002637000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000);

SELECT Id, Triplet
FROM @YourTable T
JOIN (VALUES (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34),(37),(40),(43),(46),(49)) Nums(Num) ON Num <= DATALENGTH(T.Val)
CROSS APPLY (VALUES (SUBSTRING(T.Val, Num, 3))) V(Triplet)
WHERE Triplet = 0x000000 and DATALENGTH(Triplet) = 3

What I've tried:

UPDATE x    
set  x.column =  CAST(REPLACE(x.column, 0x000000, 0xFFFFFF) AS VARBINARY)
from Table as x

Works only if the column contains no data.

UPDATE x    
set  x.attr =  CAST(REPLACE(0x000000, 0x000000, 0xFFFFFF) 
from Table as x

This one semi works by replacing the first 3 bytes of the data, but I'd prefer not to lose data in the process.

The goal is to replace sequence of empty data in varbinary(50) with sequence of my desire. Or select a specific location of the binary for example the last plausible triplet location and import/replace the data there with the desired sequence.

You can do this by running through every possible substring in order until you find a match. Then piece together the new binary value using SUBSTRING.

I have used a Nums function to generate the correct amount of rows. There are a number of different versions of this function out there, see https://sqlperformance.com/2021/01/t-sql-queries/number-series-solutions-1 You can also utilize an actual numbers table, and in SQL Server 2022 you can use GENERATE_SERIES
UPDATE t
SET Val = SUBSTRING(Val, 1, v.Num - 1) + 0xFFFFFF + SUBSTRING(Val, v.Num + 3, DATALENGTH(Val))
FROM @YourTable t
CROSS APPLY (
SELECT TOP (1)
Nums.Num
FROM dbo.Nums( CASE WHEN DATALENGTH(t.Val) > 2 THEN DATALENGTH(t.Val) - 2 END ) Nums
CROSS APPLY (VALUES (SUBSTRING(T.Val, Num, 3))) V(Triplet)
WHERE V.Triplet = 0x000000
ORDER BY
Nums.Num
) v;


If the column is declared as varbinary(max) then you can use the little-known .WRITE syntax to do this.
UPDATE t
SET Val.WRITE(0xFFFFFF, v.Num - 1, 3)
FROM @YourTable t
CROSS APPLY (
SELECT TOP (1)
Nums.Num
FROM dbo.Nums( CASE WHEN DATALENGTH(t.Val) > 2 THEN DATALENGTH(t.Val) - 2 END ) Nums
CROSS APPLY (VALUES( SUBSTRING(T.Val, Nums.Num, 3) )) V(Triplet)
WHERE V.Triplet = 0x000000
ORDER BY
Nums.Num
) v;


db<>fiddle

Note that this can only be done once per row, per UPDATE statement, you cannot do multiple replacements.

StackExchange Database Administrators Q#324715, answer score: 3