How to update string in between content using SQL?
I want to update only between text from <ActiveTo> and </ActiveTo>. so My Query Should like as below
declare @TestText varchar(MAX);
set @TestText = "<StartTimeAllowed>0000</StartTimeAllowed><StartTimeBlocked>0000</StartTimeBlocked><DaysAllowed>127</DaysAllowed><ActiveFrom>05/09/2019</ActiveFrom><ActiveTo>31-07-2022</ActiveTo><Country>428</Country><CountryCode>LV</CountryCode><Language>EN</Language><Currency>978</Currency><CurrencyCode>EUR</CurrencyCode><EmbossingLine1 /><EmbossingLine2 /><EmbossingLine3 /><AdditionalPrintText /><CardIssuance><EmbossingLine1Option>0</EmbossingLine1Option><EmbossingLine2Option>2</EmbossingLine2Option><EmbossingLine3Option>1</EmbossingLine3Option><CardTextType /><CardTextTypeReIssue /><CardPaperType /><CardEnvelopeType></CardEnvelopeType><CardPlasticType></CardPlasticType><PINTextType></PINTextType><PINTextTypeReIssue></PINTextTypeReIssue><PINPaperType></PINPaperType><PINEnvelopeType></PINEnvelopeType><CardDeliveryAddress><RequiresCard>true</RequiresCard><CompanyName></CompanyName><ContactName></ContactName><DefShip_Address1></DefShip_Address1><DefShip_Address2></DefShip_Address2><DefShip_Address3></DefShip_Address3><DefShip_Address4></DefShip_Address4><DefShip_Address5></DefShip_Address5></CardDeliveryAddress><PINDeliveryAddress><RequiresPINLetter>false</RequiresPINLetter><PINCompanyName></PINCompanyName><PINContactName></PINContactName><PINAddress1></PINAddress1><PINAddress2></PINAddress2><PINAddress3></PINAddress3><PINAddress4></PINAddress4><PINAddress5></PINAddress5></PINDeliveryAddress><ProductionQueue></ProductionQueue><SavedServiceCode></SavedServiceCode><UseDriverAddress>false</UseDriverAddress><NewDriver><LastName /><FirstName /><DriverEntityClassID /><TimeZoneID>3</TimeZoneID><XMLProperties><MidName /><EULicence /><SocialSecurityPersonnel /><SocialSecurityCompany /><EmployeeNumber /><Email /><Tel /><DriverAddress4 /><DriverAddress3 /><DriverAddress2 /><DriverAddress1 /></XMLProperties></NewDriver><NewVehicle><VehicleEntityClassID /><LastOdometer /><TankCapacity /><Description /><RegistrationNo /><EURegistered /><VehicleType /><XMLProperties><ChassisNumber /><LeasingContractReference /><LeasingCompany /><LeasingStartDate /><VehicleDate /><TargetConsumption /><HorsePower /></XMLProperties></NewVehicle></CardIssuance>";
SELECT SUBSTRING(@TestText, CHARINDEX("<ActiveTo>", @TestText) , CHARINDEX("</ActiveTo>",@TestText) - CHARINDEX("<ActiveTo>", @TestText) + Len("</ActiveTo>")) as Orignal,
replace(SUBSTRING(@TestText, CHARINDEX("<ActiveTo>", @TestText) , CHARINDEX("</ActiveTo>",@TestText) - CHARINDEX("<ActiveTo>", @TestText) + Len("</ActiveTo>")),"-","/") as Expected ,
@TestText as Before_Update,
REPLACE(@TestText,SUBSTRING(@TestText, CHARINDEX("<ActiveTo>", @TestText) , CHARINDEX("</ActiveTo>",@TestText) - CHARINDEX("<ActiveTo>", @TestText) + Len("</ActiveTo>")),
replace(SUBSTRING(@TestText, CHARINDEX("<ActiveTo>", @TestText) , CHARINDEX("</ActiveTo>",@TestText) - CHARINDEX("<ActiveTo>", @TestText) + Len("</ActiveTo>")),"-","/")) as After_Update