UPDATE	AddressesTable
SET	Field = Replace(Field, 'street', 'st')
WHERE	Field Like '%street%'

quote:

---

Originally posted by James K

UPDATE yourTable SET
	addressCol = REPLACE(REPLACE(REPLACE(addressCol,
					'street','st'),
					'avenue','ave'),
					'road','rd');

Just be aware that it will change the strings without regard to usage. State Street Global Advisors will become State st Global Advisors.

---

CREATE TABLE #A (x VARCHAR(32));
CREATE TABLE #B (o VARCHAR(32), n VARCHAR(32));

INSERT INTO #A VALUES ('This is a test'),('This is another test');

INSERT INTO #B VALUES ('This','That'),('is','was'),('test','experiment');


SELECT 'update #A set x = replace(x,'''+o+''','''+n+''')'
FROM #b;
	
DROP TABLE #A;
DROP TABLE #B;

quote:

---

Originally posted by James K

I don't know of a clean way to do this in a query without resorting to while loops or some such. One simple way would be to generate the update statements and then run them. See this example where I am trying to update the table #A with each pair of values in table #B. I would run the select statement, paste its output to a query window and run that:

CREATE TABLE #A (x VARCHAR(32));
CREATE TABLE #B (id int,o VARCHAR(32), n VARCHAR(32));

INSERT INTO #A VALUES ('This is a test'),('This is another test');

INSERT INTO #B VALUES (1,'This','That'),(2,'is','was'),(3,'test','experiment');


SELECT 'update #A set x = replace(x,'''+o+''','''+n+''')'
FROM #b;

declare @rowcount int;
select @rowcount=count(*) from #B;
while @rowcount>0
begin
   update a
      set a.x=replace(a.x,b.o,b.n)
     from (select row_number() over (order by b.id desc) as row
                 ,b.n
                 ,b.o
             from #B as b
          ) as b
          inner join #A as a
                  on a.x like '%'+b.o+'%'
    where b.row=@rowcount;
   set @rowcount=@rowcount-1;
end
	
DROP TABLE #A;
DROP TABLE #B;

---