Replace random varbinary data in middle of column (MSSQL)-CodePudding

I have a inventory column with various data and multiple rows, it's necessary replace random varbinary data in middle of column.

Example of Inventory column:

Screenshot

For example this:

0x0500420000000000005000FFFFFFFFFF56730E64FFFFFFFFFFFFFFFFFFFFFFFF0400180000000000006000FFFFFFFFFF56730E72FFFFFFFFFFFFFFFFFFFFFFFF04001E0000000000007000FFFFFFFFFF56730E5EFFFFFFFFFFFFFFFFFFFFFFFF

Need to be changed to:

0x0500420000000000005000FFFFFFFFFF56730E64FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF04001E0000000000007000FFFFFFFFFF56730E5EFFFFFFFFFFFFFFFFFFFFFFFF

I know how to change static data like here. But here is many rows and in every row data is different. It's possible maybe change data somehow by length? Start length position is 67, end 131.

CodePudding user response：

But here is many rows and in every row data is different. It's possible maybe change data somehow by length? Start length position is 67, end 131.

You can use STUFF or SUBSTRING and to rewrite the entire blob, or you can update it in-place, see Updating Blobs, eg

drop table if exists #temp
go
create table #temp(id int, blob varbinary(max))
insert into #temp(id,blob) values (1,0x0500420000000000005000FFFFFFFFFF56730E64FFFFFFFFFFFFFFFFFFFFFFFF0400180000000000006000FFFFFFFFFF56730E72FFFFFFFFFFFFFFFFFFFFFFFF04001E0000000000007000FFFFFFFFFF56730E5EFFFFFFFFFFFFFFFFFFFFFFFF)

declare @newBytes varbinary(100) = 0xAAAAAAAAAAAA

--only for varbinary(max) but updates in-place
update #temp 
  set blob.write(@newBytes,10,datalength(@newBytes))

--for varbinary(max) or varbinary(n) replace the whole value
update #temp 
  set blob = cast(STUFF(blob,30,datalength(@newBytes),@newBytes) as varbinary(max))

select * from #temp