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.
Asked by Peter
(79 rep)
Mar 13, 2023, 09:01 PM
Last activity: Mar 14, 2023, 03:18 PM
Last activity: Mar 14, 2023, 03:18 PM