Remove trailing ":" character from a row in a SQL table

Question

I have table with millions of rows. For each row, there is an nvarchar(max) column. When I populated the DB, I forgot to remove a trailing ":" character. What is the fastest / most efficient way to go through each row and remove the last character?

I'm thinking there must be a fastest way to do this than using REPLACE which seems expensive.

This is SQL Server 2008

Answer 1

You can use the STUFF function that replaces parts of a string. In this case, it is the last character.

UPDATE tbl
SET COL = stuff(COL, len(COL), 1, '')
WHERE COL > ''

Or use LEFT, taking all but the last one. The condition COL > '' ensures LEFT will have a valid length. LEFT is a shortcut in SQL Server and appears to be implemented as SUBSTRING ( see further below)*

UPDATE tbl
SET COL = LEFT(COL, len(COL) -1)
WHERE COL > ''

If you have both data with and without the trailing semicolon, you can target them specifically

UPDATE tbl
SET COL = LEFT(COL, len(COL) -1)
WHERE RIGHT(COL,1) = ':'

Here is the query plan for a query using LEFT (only top 3 lines of the text plan are shown)

select LEFT(text, LEN(text)-1), * from master..syscomments

  |--Compute Scalar(DEFINE:([Expr1061]=substring([Union1060],(1),len([Union1060])-(1))))
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1005]=(0), [Expr1007]=(0)))