RETURNS TABLE

AS RETURN

WITH L0 AS (

SELECT *

FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) Nums(Num)

)

SELECT TOP (@count)

Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM L0 a

CROSS JOIN L0 b;

(

Id INT PRIMARY KEY,

Val VARBINARY(50)

)

INSERT @YourTable

VALUES (1, 0x0329000414000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000),

(2, 0x0329002637000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000);

SELECT Id, Val FROM @YourTable T;

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 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;

SELECT Id, Val FROM @YourTable T;

(

Id INT PRIMARY KEY,

Val VARBINARY(max)

)

INSERT @YourTable

VALUES (1, 0x0329000414000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000),

(2, 0x0329002637000B14000C14000D0F00177800224600467800473C00550F00000000000000000000000000);

SELECT Id, Val FROM @YourTable T;

UPDATE t

SET Val.WRITE(0xFFFFFF, v.Num - 1, 3)

FROM @YourTable t

CROSS APPLY (

SELECT TOP (1)

Nums.Num

FROM 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;

SELECT Id, Val FROM @YourTable T;