Replace random varbinary data in middle of column (MSSQL)

Question

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.

When you say "random" do you really mean "variable" or do you actually want a section updated randomly? — Martin Smith
– Martin Smith, Commented Nov 5, 2021 at 14:40
I mean variable data in middle of column need to be changed to static data 'FF'. — Noob404
– Noob404, Commented Nov 5, 2021 at 14:49
And do you have a table that specifies Row with Id X should have start position 67 until end 131 masked out or where will this come from? — Martin Smith
– Martin Smith, Commented Nov 5, 2021 at 14:51

David Browne - Microsoft · Accepted Answer · 2021-11-05 18:07:12Z

3

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

edited Nov 5, 2021 at 18:07

answered Nov 5, 2021 at 15:17

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noob404 Over a year ago

This is what can resolve issue, with blob column working fine, but with inventory column i receive error msg: "Cannot call methods on varbinary." Seems it's because i have varbinary length limit in column properties. How to solve this?

David Browne - Microsoft Over a year ago

See updated answer.

Collectives™ on Stack Overflow

Replace random varbinary data in middle of column (MSSQL)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related