如何在 SQL 中对 Varbinary 数据执行替换 [英] How to Perform a Replace on Varbinary data in SQL

问题描述

我以前从未这样做过，但我希望对 varbinary 数据进行 SQL 替换.我正在尝试这个，但它没有成功替换，我认为是因为它将数据视为 varchar 然后转换回 varbinary.这源于这些值末尾的空格，但不是实际的空格"，因此 RTRIM 在这种情况下不起作用(下方粗体).

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

尝试更改的数据示例:

0x457874656368203430304120414320636C616D70206D657465722C2041432063757272656E74206D6F64656C20746F206D65657420796F7572206170706C69636174696F6E206E656564732E20203230303020636F756E74204C434420646973706C61792E20204869676820616363757261637920666F722063757272656E74206D6561737572656D656E74732E2020302E3922202832336D6D29206A61772073697A65206163636F6D6D6F646174657320636F6E647563746F727320757020746F203330304D434D2E2020436F6E74696E756974792062656570657220616E642064696F646520746573742E20204461746120686F6C6420616E64206D617820686F6C642E20204F7665726C6F61642070726F74656374696F6E20666F7220616C6C2072616E6765732E20204F76657272616E676520616E64206C6F77206261747465727920696E64696361746F72732E20204175746F72616E67696E672077697468206175746F20706F776572206F66662E的 0D0A090909090909090909090D0A090909090909090909090D0A090909090909090909090D0A09090909090909090909

0x457874656368203430304120414320636C616D70206D657465722C2041432063757272656E74206D6F64656C20746F206D65657420796F7572206170706C69636174696F6E206E656564732E20203230303020636F756E74204C434420646973706C61792E20204869676820616363757261637920666F722063757272656E74206D6561737572656D656E74732E2020302E3922202832336D6D29206A61772073697A65206163636F6D6D6F646174657320636F6E647563746F727320757020746F203330304D434D2E2020436F6E74696E756974792062656570657220616E642064696F646520746573742E20204461746120686F6C6420616E64206D617820686F6C642E20204F7665726C6F61642070726F74656374696F6E20666F7220616C6C2072616E6765732E20204F76657272616E676520616E64206C6F77206261747465727920696E64696361746F72732E20204175746F72616E67696E672077697468206175746F20706F776572206F66662E0D0A090909090909090909090D0A090909090909090909090D0A090909090909090909090D0A09090909090909090909

脚本:

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

推荐答案

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

这篇关于如何在 SQL 中对 Varbinary 数据执行替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！