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

10

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.

    
asked by anonymous 10.02.2014 / 16:49

4 answers

14

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

    
10.02.2014 / 16:49
5

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%'
    
14.02.2014 / 13:59
5

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

    
26.05.2014 / 16:24
1

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

    
30.10.2017 / 20:22