Using Replace And Convert with varbinary

Question

I need a query that will find part of a string in a varbinary and remove on the requested part of the string

For example my table dbo.inventory_table has to columns CharacterIdx and Data. Data is the target column which contains varbinary so for characteridx 101756 data is this

0x2105000000000000430000000000000000003C090000000000002C0100000200000000F83D09000000000000580200000400000000F83E09000000000000E80300000600000000F8

I want a query to find and remove

3C090000000000002C0100000200000000F83D09000000000000580200000400000000F83E09000000000000E80300000600000000F8

but leave

0x210500000000000043000000000000000000

Where CharacterIdx = 101756

I have tried

UPDATE [dbo].[Inventory_table]
SET Data = REPLACE(Data, '3C090000000000002C0100000200000000F83D09000000000000580200000400000000F83E09000000000000E80300000600000000F8', '')
WHERE CharacterIdx = 101756

But it gives me an error:

Msg 257, Level 16, State 3, Line 3

Implicit conversion from data type varchar to varbinary is not allowed.

Use the CONVERT function to run this query.

I need the string to remain varbinary though.

As the second parameter of REPLACE, use 0x instead of ''. You should also don't put the first parameter between quotes; instead of this, prefix it with 0x. stackoverflow.com/questions/24494778/… — Caffé
– Caffé, Commented Oct 6, 2014 at 22:32
Those 000000 are going to cause you problems as well probably. As that is a NULL character. You might need COLLATE Latin1_General_100_BIN2 — Martin Smith
– Martin Smith, Commented Oct 6, 2014 at 22:50

jpw · Accepted Answer · 2014-10-06 22:55:54Z

1

I believe that you need to convert it back to varbinary asreplacereturns avarcharvalue from implicit conversion. Try this:

UPDATE [dbo].[Inventory_table]
SET Data = CAST(REPLACE(Data, 0x3C090000000000002C0100000200000000F83D09000000000000580200000400000000F83E09000000000000E80300000600000000F8, 0x) AS varbinary)
WHERE CharacterIdx = 101756

On my server this changes the value to:

0x210500000000000043000000000000000000

edited Oct 6, 2014 at 22:55

answered Oct 6, 2014 at 22:36

jpw

45k6 gold badges71 silver badges90 bronze badges

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

2 Comments

Nathan Aguliar Over a year ago

yea thats what im trying to get but that query didnt remove the rest, but it ran successfully

Nathan Aguliar Over a year ago

oh with the 0x revision? no let me try that now

Collectives™ on Stack Overflow

Using Replace And Convert with varbinary

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