Search code examples
sqlsql-servert-sqlvarbinary

Using Replace And Convert with varbinary

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.

Solution

  • 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