How to replace duplicate words in a column with just one word in SQL Server

Question

I have a few million strings that relate to file paths in my database; due to a third party program these paths have become nested like below:

C:\files\thirdparty\thirdparty\thirdparty\thirdparty\thirdparty\thirdparty\unique_bit_here\

I want update the entries so that thirdparty\thirdparty\etc becomes \thirdparty.

I have tried this code:

UPDATE table
SET Field = REPLACE(Field, 'tables\thirdparty\%thirdparty\%\', 'tables\thirdparty\')

Answer 1

WHILE EXISTS (SELECT * FROM table WHERE Field LIKE '%\thirdparty\thirdparty\%')
BEGIN
    UPDATE table SET Field = REPLACE(Field, '\thirdparty\thirdparty\', '\thirdparty\')
END