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

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 an开发者_StackOverflow中文版d 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

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)))

Technically, there is a way without using replace if you can guarantee that the last character is the one that should be removed:

Update Table
Set FatField = Substring(FatField, 1, DataLength(FatField)/2 - 1)

However, as Mitch Wheat suggested, if you have that many rows, you'll want to break it up into batches.

Use SUBSTRING or STUFF as @Thomas suggested, but Disk I/O will almost certainly outweigh any small in-memory improvements with that many rows.

I would suggest performing in a batch of, say, 100,000 rows at a time. This will reduce the total transaction size.

You could also place the DB temporarily in SIMPLE recovery mode which should be faster, but talk to your DBA, as this has implications...

For resource optimization, the below would be most efficient:

UPDATE MyTable
SET FatField = Reverse(SubString(Reverse(FatField),2,8000))
WHERE Right(FatField,1) = ':'

Gotta love "reverse". It's rarely used, but is very powerful and provides solution to what may otherwise be complex needs.