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 '%|||';