How to replace column text in SQL Server (tsql)?

Question

I have a text column in SQL Server 2008 R2, which is filled with wrong typed text, in thousands of records. I would like to make a UPDATE replacing the wrong text with the correct one.

Answer 1

It is possible to use the function REPLACE tsql, as follows in an UPDATE command:

UPDATE nomeTabela
  SET colunaTexto = REPLACE ( colunaTexto , 'tetxo-erraddo' , 'texto-correto' )

Also, if you want to update only the required records, use a WHERE clause, filtering the records using LIKE:

UPDATE nomeTabela
  SET colunaTexto = REPLACE ( colunaTexto , 'tetxo-erraddo' , 'texto-correto' )
  WHERE colunaTexto LIKE '%tetxo-erraddo%'

Reference: Using REPLACE in an UPDATE statement

Answer 2

I find it interesting to carry out updates, however simple they may be, as follows:

update t
set nomeColuna = replace(nomeColuna, 'xx', 'yy')
--select * --select pode ser executado e trará os mesmos registros que o update atualizará
from nomeTabela as t
where t.nomeColuna like '%texto_procurado%'

This type of update avoids, in many cases, the famous update without Where, because when executing only the first two lines gives execution error because there is no table with name "t" in the database (alias used only at runtime).

Answer 3

Miguel Angelo’s answer seems correct. I would just like to add that before you launch an Update, make the Selects you need to fine tune your Where condition, until you are sure your filter is selecting exactly the records you are looking for, no more, no less. Something of the style:

SELECT colunaTexto 
FROM nomeTabela 
WHERE colunaTexto LIKE '%texto_errado%'

Answer 4

name if I just want to correct the register for Nfe issuance, then I have to exchange accented vowels, ç, ", etc ...

I’m using:

UPDATE TABELA CAMPO = REPLACE(CAMPO, 'º', 'o')

Abs