Find and replace string values in a table

1

Hello,

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.

2

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

3

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

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

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.

Thanks

5

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

6

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')
where
	YourColumn1 like '%Sys0001%';
	
update YourTable set
	YourColumn2 = replace(YourColumn2,'Sys0001','Sys1111')
where
	YourColumn2 like '%Sys0001%';
	
update YourTable set
	YourColumn3 = replace(YourColumn3,'Sys0001','Sys1111')
where
	YourColumn3 like '%Sys0001%';
7

Thanks James.
Works great.