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)))
