How to Perform a Replace on Varbinary Data in SQL

How to Perform a Replace on Varbinary data in SQL

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

Using Replace And Convert with varbinary

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

Replace 00 with varbinary type

I must admit, that I didn't really get what you tried to achieve. This might be a case of an xy problem...

But - as far as I can understand - you want to transform this varbinaries to readable text, by getting rid of unreadable characters. One trick might be this:

DECLARE @MyTable TABLE(id int identity(1,1) NOT NULL PRIMARY KEY,val varbinary(max));

INSERT INTO @MyTable(val)
SELECT 0x4D5A9304FFFFB8408E1FBA0EB409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2450454C010355CBC65CE2210B010B0A06DE2920401022040482034085101010101090294B40B8026C280820482E74657874E4092A0220602E72737263B802440C40402E72656C6F630C62104042C0294802051C26740301C0205A050332557E040A6F050A1A2E447E040A6F050A1C2E377E040A6F050A1E2E2A7E040A6F050A1F0B2E1C7E040A6F050A1F162E0E7E040A6F050A1F293302162A172A1E0228060A2A5605FFFE3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D227574662D3136223F3E0D0A3C4C69746543616C6C6261636B47656E657261746F7220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D612D696E7374616E63652220786D6C6E733A7873643D22687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61223E0D0A20203C416374696F6E47726F75703E0D0A202020203C436F6E646974696F6E3E696620280A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D203429207C7C202A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D203629207C7C202A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D203829207C7C202A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D20313129207C7C202A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D20323229207C7C202A2852756E74696D652E436F6E66696775726174696F6E44696374696F6E6172792E4E756D65724F70657261746F7261203D3D20343129202A290A7B0A72657475726E2066616C73653B0A7D0A656C73650A7B0A72657475726E20747275653B0A7D3C2F436F6E646974696F6E3E0D0A202020203C4E616D653E4E6F77612067727570613C2F4E616D653E0D0A20203C2F416374696F6E47726F75703E0D0A3C2F4C69746543616C6C6261636B47656E657261746F723E42534A4201010C76342E302E3330333139056C2401237E91380123537472696E6773C80208235553D2102347554944E29423426C6F62020147140901FA2533160106020206030102010A0106362F0A676106AC8C06CC8C0AF7610AFF6101010101101B0501015020963D0A01B1208618520E0111521B19522521520E29FF2A3117012E09520E2E0B322E136C2E1B750480EA040126E3071258012A013C4D6F64756C653E48796472615F526573756C742E646C6C436F6E646974696F6E736D73636F726C696253797374656D4F626A656374436F6E646974696F6E5F4E6F77615F67727570612E63746F7243646E4879647261487964726143616C6C6261636B417373656D626C794465736372697074696F6E41747472696275746553797374656D2E52756E74696D652E436F6D70696C65725365727669636573436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746552756E74696D65436F6D7061746962696C69747941747472696275746548796472615F526573756C7452756E74696D65436F6E66696775726174696F6E44696374696F6E6172796765745F4E756D65724F70657261746F72616465736372697074696F6E0320252267F49CC8B641B90894AB9E62023108B77A5C561934E08903020321084B3653AA0A875D040926010E0E0E0E0E0E04210108030612190328390110546573746F776150726F66696C7465720841545543484F4C5303312E3A323031392E302E302E3A32392D30342D323031390801081E0101540216577261704E6F6E457863657074696F6E5468726F777301B829CE2920C0295F436F72446C6C4D61696E6D73636F7265652E646C6CFF2520111018810130814858405C025C023456535F56455253494F4E5F494E464FBD04EFFE013F0402440156617246696C65496E666F24045472616E736C6174696F6EB4BC0101537472696E6746696C65496E666F98010130303030303462302C020146696C654465736372697074696F6E20380146696C6556657273696F6E302E302E302E30441101496E7465726E616C4E616D6548796472615F526573756C742E646C6C2802014C6567616C436F70797269676874204C11014F726967696E616C46696C656E616D6548796472615F526573756C742E646C6C34080150726F6475637456657273696F6E302E302E302E30380801417373656D626C792056657273696F6E302E302E302E302CE039 
UNION ALL
SELECT 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030055CBC65C0000000000000000E00002210B010B00000A00000006000000000000DE290000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000902900004B00000000400000B802000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E409000000200000000A000000020000000000000000000000000000200000602E72737263000000B80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000C02900000000000048000000020005001C260000740300000100000000000000C02000005A050000000000000000000000000000000000000000000000000000000000000000000000000000000000000330020055000000000000007E0400000A6F0500000A1A2E447E0400000A6F0500000A1C2E377E0400000A6F0500000A1E2E2A7E0400000A6F0500000A1F0B2E1C7E0400000A6F0500000A1F162E0E7E0400000A6F0500000A1F293302162A172A1E02280600000A2A0000000000000056050000FFFE3C003F0078006D006C002000760065007200730069006F006E003D00220031002E0030002200200065006E0063006F00640069006E0067003D0022007500740066002D003100360022003F003E000D000A003C004C00690074006500430061006C006C006200610063006B00470065006E0065007200610074006F007200200078006D006C006E0073003A007800730069003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E00630065002200200078006D006C006E0073003A007800730064003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D00610022003E000D000A00200020003C0041006300740069006F006E00470072006F00750070003E000D000A0020002000200020003C0043006F006E0064006900740069006F006E003E0069006600200028000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D0020003400290020007C007C00200020000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D0020003600290020007C007C00200020000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D0020003800290020007C007C00200020000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D00200031003100290020007C007C00200020000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D00200032003200290020007C007C00200020000A002800520075006E00740069006D0065002E0043006F006E00660069006700750072006100740069006F006E00440069006300740069006F006E006100720079002E004E0075006D00650072004F00700065007200610074006F007200610020003D003D002000340031002900200020000A0029000A007B000A00720065007400750072006E002000660061006C00730065003B000A007D000A0065006C00730065000A007B000A00720065007400750072006E00200074007200750065003B000A007D003C002F0043006F006E0064006900740069006F006E003E000D000A0020002000200020003C004E0061006D0065003E004E006F00770061002000670072007500700061003C002F004E0061006D0065003E000D000A00200020003C002F0041006300740069006F006E00470072006F00750070003E000D000A003C002F004C00690074006500430061006C006C006200610063006B00470065006E0065007200610074006F0072003E00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000024010000237E0000900100003801000023537472696E677300000000C80200000800000023555300D0020000100000002347554944000000E00200009400000023426C6F620000000000000002000001471400000901000000FA25330016000001000000060000000200000002000000060000000300000001000000020000000100000000000A00010000000000060036002F000A00670061000600AC008C000600CC008C000A00F70061000A00FF0061000000000001000000000001000100010010001B00000005000100010050200000000096003D000A000100B12000000000861852000E000100110052001B00190052002500210052000E002900FF002A00310017012E00090052000E002E000B0032002E0013006C002E001B007500048000000000000000000000000000000000EA0000000400000000000000000000000100260000000000E30700000000000000000000120058000000000000000000010000002A0100000000003C4D6F64756C653E0048796472615F526573756C742E646C6C00436F6E646974696F6E73006D73636F726C69620053797374656D004F626A65637400436F6E646974696F6E5F4E6F77615F6772757061002E63746F720043646E48796472610048796472610043616C6C6261636B417373656D626C794465736372697074696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650048796472615F526573756C740052756E74696D6500436F6E66696775726174696F6E44696374696F6E617279006765745F4E756D65724F70657261746F7261006465736372697074696F6E0000000003200000000000252267F49CC8B641B90894AB9E6202310008B77A5C561934E0890300000203200001084B3653AA0A875D04092006010E0E0E0E0E0E0420010108030612190320000839010010546573746F776150726F66696C746572000841545543484F4C5303312E300A323031392E302E302E300A32392D30342D3230313900000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301B82900000000000000000000CE290000002000000000000000000000000000000000000000000000C02900000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000044001100010049006E007400650072006E0061006C004E0061006D0065000000480079006400720061005F0052006500730075006C0074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000480079006400720061005F0052006500730075006C0074002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

;WITH SingleCharacters AS
(
    SELECT t.id
          ,Nmbr
          ,OneChar
    FROM @MyTable t
    CROSS APPLY(SELECT TOP(DATALENGTH(t.val)/2) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values v1 CROSS JOIN master..spt_values v2) A(Nmbr)
    CROSS APPLY(SELECT CAST(SUBSTRING(t.val,(-1+Nmbr*2),2) AS VARCHAR(1))) B(OneChar)
)
SELECT sc.id
      ,(
        SELECT sc2.OneChar AS [*]
        FROM SingleCharacters sc2
        WHERE sc2.id=sc.id
        AND ASCII(sc2.OneChar) BETWEEN 32 AND 126
        ORDER BY sc2.Nmbr
        FOR XML PATH(''),TYPE).value('.','varchar(max)')
FROM SingleCharacters sc
GROUP BY sc.id;

The idea in short:

The characters are taken from the VARBINARY with SUBSTRING at any odd position we pick two bytes. This is casted to VARCHAR(1).

The final SELECT will use a GROUP BY together with a correlated sub-query. Starting with v2017 you should use STRING_AGG() for the same. This will re-concatenate alle characters with ASCII-values between 32 and 126, hence most of the plain latin characters.

For the given binaries the result is:

ML!hspormcno erni O oePL\! @)@( .et`rrD@.eoB)t 2~Do.~*ooo)***?m eso=10 noig"t-6?<iealakeeao mn:s=ht:/w.3og20/MShm-ntne mn:s=ht:/w.3og20/MShm" <cinru>  Cniini RnieCniuainitoayNmrprtr =4 |*RnieCniuainitoayNmrprtr =6 |*RnieCniuainitoayNmrprtr =8 |*RnieCniuainitoayNmrprtr =1)| (utm.ofgrtoDcinr.ueOeaoa= 2 |*RnieCniuainitoayNmrprtr =4)*eunflele{rtr re<Cniin  <aeNw rp<Nm> /cinru>/iealakeeao>SBv..01$#Srns#SGI#lb%/ga = R!1.lMdl>yr_eutdlodtossolbytmbetodto_oaguacoCnyrHdaalaksebyecitoAtiueytmRnieCmieSrieCmiaineaainAtiueutmCmaiiiytrbtHdaRslRnieofgrtoDcinrgtNmrprtrdsrpin "Ab1\6&9etwPoitrAUHL.21...2-421TrpoEcpinhos )CrlMimcredl%X\\4SVRINIFDVrienornltoSrnFlIf0040FlDsrpin8FlVrin...Dnenlaeyr_eutdlLgloyih OiiaFlnmHdaRsl.l4rdcVrin...8sebyVrin...,
M@!Ti rga antb u nDSmd.$PLU@KH.et .sc@.eo@HtZU~Do.~*ooo)***V<?xml version="1.0" encoding="utf-16"?><LiteCallbackGenerator xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">  <ActionGroup>    <Condition>if ((Runtime.ConfigurationDictionary.NumerOperatora == 4) ||  (Runtime.ConfigurationDictionary.NumerOperatora == 6) ||  (Runtime.ConfigurationDictionary.NumerOperatora == 8) ||  (Runtime.ConfigurationDictionary.NumerOperatora == 11) ||  (Runtime.ConfigurationDictionary.NumerOperatora == 22) ||  (Runtime.ConfigurationDictionary.NumerOperatora == 41)  ){return false;}else{return true;}</Condition>    <Name>Nowa grupa</Name>  </ActionGroup></LiteCallbackGenerator>BJv..01l$#8#tig#S#UD#lbG%6/gaaaP=RRR%!R)*1.R.2.l.u&X*Mdl>HdaRsl.lodtosmcriytmOjcodto_oagua.trCnyryralaksebyecitoAtiueSse.utm.oplrevcsCmiaineaainAtiueRnieoptbltAtiueHdaRslutmofgrtoDcinre_ueOeaoadsrpin %g\6  9TsoarfleAUHL.090002-421TrpoEcpinhos_oDlanmcredl0HX\\4VS_VERSION_INFO?DVarFileInfo$TranslationStringFileInfo000004b0,FileDescription 0FileVersion0.0.0.0DInternalNameHydra_Result.dll(LegalCopyright LOriginalFilenameHydra_Result.dll4ProductVersion0.0.0.08Assembly Version0.0.0.0

This looks not so bad in my eyes.

Hint: You can use some kind of CASE list or a mapping table to replace certain ASCII values with the appropriate character.

Replace random varbinary data in middle of column (MSSQL)

But here is many rows and in every row data is different. It's possible maybe change data somehow by length? Start length position is 67, end 131.

You can use STUFF or SUBSTRING and + to rewrite the entire blob, or you can update it in-place, see Updating Blobs, eg

drop table if exists #temp
go
create table #temp(id int, blob varbinary(max))
insert into #temp(id,blob) values (1,0x0500420000000000005000FFFFFFFFFF56730E64FFFFFFFFFFFFFFFFFFFFFFFF0400180000000000006000FFFFFFFFFF56730E72FFFFFFFFFFFFFFFFFFFFFFFF04001E0000000000007000FFFFFFFFFF56730E5EFFFFFFFFFFFFFFFFFFFFFFFF)

declare @newBytes varbinary(100) = 0xAAAAAAAAAAAA

--only for varbinary(max) but updates in-place
update #temp 
  set blob.write(@newBytes,10,datalength(@newBytes))

--for varbinary(max) or varbinary(n) replace the whole value
update #temp 
  set blob = cast(STUFF(blob,30,datalength(@newBytes),@newBytes) as varbinary(max))

select * from #temp 

t-sql: replace function truncates when binary data type is used

The issue is that binary(50) will pad with 0x00 to reach the fixed length of 50 and that is generally treated as a string terminator.

You see the same behaviour with

SELECT N'The quick brown ' +  NCHAR(0) + N' fox jumped over the lazy dog.'

The data is actually still there after the REPLACE. It is in the attempt to display it as a string that truncation occurs.

DECLARE @VBIN BINARY(50)
DECLARE @PASS NVARCHAR(3)
DECLARE @TEXT NVARCHAR(MAX)

SET @TEXT = '123456123789'
SET @PASS = '123'
SET @VBIN = CONVERT(BINARY, N'321')

SELECT REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN))
SELECT DATALENGTH(REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN))) /*112*/

SELECT CAST(REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN)) AS VARBINARY(112))

Using varbinary rather than binary would avoid the issue but I'm not sure what you are actually trying to do here anyway.

How to replace legacy datatypes in application working via OLEDB?

If the app code simply uses the column values as large value types, no code changes are likely needed. Changes are required if the code uses TEXTPTR, READTEXT, WRITETEXT, etc. (directly or via API method) as these T-SQL elements only operate on the legacy types.

---

Related Topics