How to Perform a Replace on Varbinary data in SQL

Question

I've never had to do this before but I'm looking to do a SQL replace on varbinary data. I'm trying this but it's not successfully replacing, I think because it's treating the data as varchar and then cast back to varbinary. This stemmed from blank spaces at the end of these values but are not actual 'spaces' so RTRIM doesn't work in this scenario (bold below).

Example of Data trying to change:

0x457874656368203430304120414320636C616D70206D657465722C2041432063757272656E74206D6F64656C20746F206D65657420796F7572206170706C69636174696F6E206E656564732E20203230303020636F756E74204C434420646973706C61792E20204869676820616363757261637920666F722063757272656E74206D6561737572656D656E74732E2020302E3922202832336D6D29206A61772073697A65206163636F6D6D6F646174657320636F6E647563746F727320757020746F203330304D434D2E2020436F6E74696E756974792062656570657220616E642064696F646520746573742E20204461746120686F6C6420616E64206D617820686F6C642E20204F7665726C6F61642070726F74656374696F6E20666F7220616C6C2072616E6765732E20204F76657272616E676520616E64206C6F77206261747465727920696E64696361746F72732E20204175746F72616E67696E672077697468206175746F20706F776572206F66662E0D0A090909090909090909090D0A090909090909090909090D0A090909090909090909090D0A09090909090909090909

Script:

update digitalassetcontent 
set content = (CAST(REPLACE(content, '0D0A09090909090909090909', '') as varbinary(MAX)))

Try SELECT REPLACE(Content, 0x304430413039303930393039303930393039303930393039304430413039 , 0x) FROM digitalassetcontent Does this give you the result u are looking for? — TMNT2014
– TMNT2014, Commented Jun 30, 2014 at 16:43
Yes, I was forgetting the 0x. Not sure why this needed and would like to understand. Thanks for the advice! — Matt Weick
– Matt Weick, Commented Jun 30, 2014 at 16:50

Anon · Accepted Answer · 2014-06-30 16:44:38Z

2

update digitalassetcontent 
set content = REPLACE(content,0x0D0A09090909090909090909,0x)

answered Jun 30, 2014 at 16:44

Anon

10.9k1 gold badge32 silver badges31 bronze badges

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

2 Comments

Matt Weick Over a year ago

Thank you for showing me the logic behind it. Anyway you could explain why the 0x is needed? Hexadecimal thing?

Anon Over a year ago

0x is a representation of a zero-length binary string, just like '' is used to indicate a zero-length character string.

Collectives™ on Stack Overflow

How to Perform a Replace on Varbinary data in SQL

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