SQLTeam.com | Weblogs | Forums

Find and replace string values in a table




I need to replace a string value in the table. For example I want to have a query to replace all 'Sys0001' text to 'Sys1111' in the specific table.

Thanks for any help.


declare @word varchar(20)='Sys0001'
select @word
set @word=REPLACE(@word, 'Sys0001', 'Sys1111')
select @word


Use an update statement to update the data in the table.

update YourTable set
	YourColumn = replace(YourColumn,'Sys0001','Sys1111')
	YourColumn like '%Sys0001%';


Hi shilpash,

Thanks for your help. I am not sure where I can place the table in your query. For example if my table name is MyTable.



Hi James,
Thanks for replying.
What happen if my table has 40 columns?


If you have forty columns and if you want to replace the string in every column, you will have to do the update to all forty columns. For example,

update YourTable set
	YourColumn1 = replace(YourColumn1,'Sys0001','Sys1111'),
	YourColumn2 = replace(YourColumn2,'Sys0001','Sys1111'),
	YourColumn3 = replace(YourColumn3,'Sys0001','Sys1111');
-- or
update YourTable set
	YourColumn1 = replace(YourColumn1,'Sys0001','Sys1111')
	YourColumn1 like '%Sys0001%';
update YourTable set
	YourColumn2 = replace(YourColumn2,'Sys0001','Sys1111')
	YourColumn2 like '%Sys0001%';
update YourTable set
	YourColumn3 = replace(YourColumn3,'Sys0001','Sys1111')
	YourColumn3 like '%Sys0001%';


Thanks James.
Works great.