SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating data in middle of field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newbie_al
Starting Member

3 Posts

Posted - 09/06/2013 :  15:57:37  Show Profile  Reply with Quote
I have a table with 100,000 addresses and I need to change 'street' to 'st', 'avenue' to 'ave' and 'road' to 'rd'. These words can appear anywhere in the field.

example

10 Main street & Washington avenue

Trying to get it to be

10 Main st & Washington ave

Thanks for help

jethrow
Starting Member

USA
37 Posts

Posted - 09/06/2013 :  16:11:49  Show Profile  Reply with Quote
Here's an example for street->st. You can change the search/replace string as needed:
UPDATE	AddressesTable
SET	Field = Replace(Field, 'street', 'st')
WHERE	Field Like '%street%'


Microsoft SQL Server Noobie

Edited by - jethrow on 09/06/2013 16:14:35
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/06/2013 :  16:13:15  Show Profile  Reply with Quote
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.
Go to Top of Page

newbie_al
Starting Member

3 Posts

Posted - 09/06/2013 :  17:21:43  Show Profile  Reply with Quote
Thanks James and Jethrow they both worked great. Is there a way of doing these updates based on another table. I only gave 3 examples of approx 200 other words I need to replace. I can pull together a table containing old(original data) and new(data to be changed to) fields.

Thanks

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.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/06/2013 :  17:42:57  Show Profile  Reply with Quote
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 (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;
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

319 Posts

Posted - 09/06/2013 :  20:13:21  Show Profile  Reply with Quote
Looping could be done like this (see red remark below in James' code).
I know is't not pretty, but it works:
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;



By the way, be very carefull with this, as this will not only replace entire words - it will replace part of words, so if you replace "is" with "was", before you replace "This" with "That" in James' example, you'd get "Thwas was a experiment" and "Thwas was a experiment".
Go to Top of Page

newbie_al
Starting Member

3 Posts

Posted - 09/10/2013 :  14:14:23  Show Profile  Reply with Quote
Thanks. Ran into another issue. The replace works to well it is replacing words that I didn't want.

EX- I want to replace 'WAY' with 'WY'
Toms Way changed to Toms WY

the problem I'm having is that it is changing

Broadway to BroadWY.

Is there a way to replace only if there is a space before and after the word?

Thanks


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000