Dansmith Dansmith - 4 years ago 164
SQL Question

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

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 Source
WHILE EXISTS (SELECT * FROM table WHERE Field LIKE '%\thirdparty\thirdparty\%')
BEGIN
    UPDATE table SET Field = REPLACE(Field, '\thirdparty\thirdparty\', '\thirdparty\')
END
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download