sqlsql-serverdirty-data

Delete fixed string contained in SQL Server Table Column


I have a table with filed 'myData' of type nvarchar containing sometimes a data ending with the string '|||' that I want to remove. Obviously the data is not fixed, so I can't just use

UPDATE myTable
SET myData = REPLACE(myData, 'oldString', 'newString')

as this would work just for one record (e.g. oldString = '12-feb-17|||' and newString = '12-feb-17')

How can I do it globally?


Solution

  • You can do:

    UPDATE myTable
        SET myData = LEFT(myData, LEN(myData) - 3)
        WHERE myDATE LIKE '%|||';