TSQL Replace large block of text

I have a database that is storing xml data in a nvarchar(max). Here is a part of the data that is stored.

  <Groups />    <Groups2 />    <pairs>      <p>        <Name>AllowPeerDownload</Name>        <Val>false</Val>      </p>      <p>        <Name>AllowUserCancelScan</Name>        <Val>false</Val>      </p>      <p>        <Name>AutoCloseTimeout</Name>        <Val>60</Val>      </p>      <p>        <Name>CalculateHashes</Name>        <Val>false</Val>      </p>      <p>        <Name>DayOfMonthEnabled_0</Name>        <Val>false</Val>      </p>    </pairs>    <Default>false</Default>  

It works for names that start with rep% but it makes the rest nulls.

      UPDATE AgentBehavior      SET BehaviorXML = ( SELECT           LEFT(BehaviorXML, CHARINDEX('<pairs>', BehaviorXML) +7)           +     '<p>        <Name>AllowPeerDownload</Name>        <Val>True</Val>      </p>      <p>        <Name>AllowUserCancelScan</Name>        <Val>false</Val>      </p>      <p>        <Name>AutoCloseTimeout</Name>        <Val>60</Val>      </p>      <p>        <Name>CalculateHashes</Name>        <Val>false</Val>      </p>      <p>        <Name>DayOfMonthEnabled_0</Name>        <Val>True</Val>      </p>'                    + SUBSTRING(BehaviorXML, CHARINDEX('</pairs', BehaviorXML), LEN(BehaviorXML))            FROM    AgentBehavior AB          where AgentBehavior.name = ab.name    and name like 'Rep%' )  

This updates the ones that I want but it makes updates the ones that do not start with rep% to nulls. Any help would be greatly appreciated. xAFGuy

https://stackoverflow.com/questions/66165155/tsql-replace-large-block-of-text February 12, 2021 at 09:05AM