MSSQL: Replace text (typo)
Hi,
I have a table with 3 columns, this table currently has +18000 data rows. In one of the columns I manage a 'log' that identifies whether status has been change to "Activo" ('Active' in english) or "Bloqueado" ('Locked' in english). As an example, I can say the full text, per row, sees like "Cambio a estado (2) Bloqueado"
We have found that many rows have a typo, instead of saying "...Bloqueado" it says "...Bloquedao" so we want to fix it.
How can we update all rows that contains "Bloquedao", replace it by "Bloqueado" and keep all other text exactly as it is?
Image attached.
Captura.PNG
I have a table with 3 columns, this table currently has +18000 data rows. In one of the columns I manage a 'log' that identifies whether status has been change to "Activo" ('Active' in english) or "Bloqueado" ('Locked' in english). As an example, I can say the full text, per row, sees like "Cambio a estado (2) Bloqueado"
We have found that many rows have a typo, instead of saying "...Bloqueado" it says "...Bloquedao" so we want to fix it.
How can we update all rows that contains "Bloquedao", replace it by "Bloqueado" and keep all other text exactly as it is?
Image attached.
Captura.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Vitor That update looks scary :(
Shouldn't I have to add a conditional? like:
Shouldn't I have to add a conditional? like:
WHERE log like '%bloquedao%'
Jose, it will not harm
UPDATE TableName
SET accion=REPLACE(accion, 'Bloquedao', 'Bloqueado')
from TableName
WHERE accion like '%bloquedao%'
UPDATE TableName
SET accion=REPLACE(accion, 'Bloquedao', 'Bloqueado')
from TableName
WHERE accion like '%bloquedao%'
ASKER
Good solution.
I added contintional 'Where' to reduce risk.
Thanks a lot.
I added contintional 'Where' to reduce risk.
Thanks a lot.
Well, the REPLACE will only occur on the records that has the typo but if you feel more comfortable with the WHERE clause won't harm.
Btw, Scott's comment is correct. Having a table with the descriptions would make your life easier.
Cheers.
Btw, Scott's comment is correct. Having a table with the descriptions would make your life easier.
Cheers.
ASKER
Thanks. Good comments.
Instead have a code for standard changes. For example, 1 = "Cambio a estado (2) Bloqueado" or whatever the standard message is. You could have a "custom" code, such as 32767, and store actual text for very rare changes where you need to store a custom description.