I have a text column in SQL Server 2008 R2, which is populated with wrong typed text in thousands of records. I would like to make a UPDATE
replacing the wrong text with the correct one.
I have a text column in SQL Server 2008 R2, which is populated with wrong typed text in thousands of records. I would like to make a UPDATE
replacing the wrong text with the correct one.
You can use the REPLACE function of tsql, as follows in an UPDATE command:
UPDATE nomeTabela
SET colunaTexto = REPLACE ( colunaTexto , 'tetxo-erraddo' , 'texto-correto' )
In addition, 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
Miguel Angelo's answer seems correct. I would just like to add that before you launch into an Update, make the Selects that are needed to fine-tune your Where condition until you are sure that 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%'
I find it interesting to do 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 prevents in many cases the famous update without where, because running only the first two lines gives execution error because there is no table named "t" in the database (alias used only at run time).
If I just want to correct the registration for NFe, then I have to change accented vowels, ç, "etc ...
I'm using:
UPDATE TABELA CAMPO = REPLACE(CAMPO, 'º', 'o')
Abs