0

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)))
ngrashia
  • 9,869
  • 5
  • 43
  • 58
Matt Weick
  • 332
  • 6
  • 19
  • 1
    Try SELECT REPLACE(Content, 0x304430413039303930393039303930393039303930393039304430413039 , 0x) FROM digitalassetcontent Does this give you the result u are looking for? – TMNT2014 Jun 30 '14 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 Jun 30 '14 at 16:50

1 Answers1

2
update digitalassetcontent 
set content = REPLACE(content,0x0D0A09090909090909090909,0x)
Anon
  • 10,660
  • 1
  • 29
  • 31
  • Thank you for showing me the logic behind it. Anyway you could explain why the 0x is needed? Hexadecimal thing? – Matt Weick Jun 30 '14 at 16:49
  • `0x` is a representation of a zero-length binary string, just like `''` is used to indicate a zero-length character string. – Anon Jun 30 '14 at 16:52