Im trying to update a set of strings I have with duplicate values but I only want to update the “second” set of duplicate values and not the first. This value stored in a string field in a SQL Server Database. Here are the strings in question:

Standard Activity Note | Type of Recovery: Final | Date of Member Settlement: 12/20/2023 | TPL: 10/06/2023 | UM: | UIM: 12/20/2023 | n/a | Total amount of member settlement: $30,755.00 | TPL: $22,275.00 | UM: | UIM: $8,500.00 | n/a |

Standard Activity Note | Type of Recovery: Final | Date of Member Settlement: 9/13/2023 | TPL: 09/13/2023 | UM: | UIM: | n/a | Total amount of member settlement: $50,000.00 | TPL: $50,000.00 | UM: | UIM: | n/a |

Standard Activity Note | Type of Recovery: Final | Date of Member Settlement: 10/9/2023 | TPL: 10/09/2023 | UM: | UIM: | n/a | Total amount of member settlement: $7,500.00 | TPL: $7,500.00 | UM: | UIM: | n/a |

I am trying to update ONLY the part of the text where it says ‘TPL:’ and ‘UM:’ to ‘TPL Amount:’ and ‘UM Amount:’. Problem is that it exists twice in the string so if I run an update to replace substring of ‘TPL:’ it’ll update both.

Any ideas on how to update the second instance of the TPL and UM string? The string has at least pipes in it so that may help. Additionally I need the update to be more dynamic as I have about 200 rows that need to be updated and cant just update one line at a time.

Thanks for your help!

UPDATE dbo.xxx
SET Value = REPLACE(Value, ‘TPL:’, ‘TPL Amount:’)
WHERE text_description like ‘%tpl:%’

UPDATE dbo.xxx
SET Value = REPLACE(Value, ‘UM:’, ‘UM Amount:’)
WHERE text_description like ‘%UM:%’

I believe above will work but it will update both values if they exist I only want it to update the second iteration of the text in the string.