Blog Details

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 Original,
       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;