Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 search & replace
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

gongxia649
So Suave

Azores
344 Posts

Posted - 08/09/2006 :  09:28:13  Show Profile  Visit gongxia649's Homepage  Reply with Quote
how will i do it only for the right most word? because its replacing whatever it finds in the street name.

field
----------------
road avenue road
road avenue

field
-----------------
road avenue rd
road ave


thanks

Edited by - gongxia649 on 08/09/2006 09:33:20
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/09/2006 :  10:06:00  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i got something like this but
i ahve this error

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.


update address1
set ad_str1 = replace(a.ad_str1, st.tx_description, st.cd_street_type)
from address1 a, street_type st
where a.ad_str1 like '%' + st.tx_description + '%'
and reverse(substring(reverse(a.ad_str1), 1, charindex(' ', reverse(a.ad_str1))))

Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 08/09/2006 :  15:17:23  Show Profile  Reply with Quote
You have an extra ) at the end of the statement.

Ken
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/09/2006 :  19:05:01  Show Profile  Visit gongxia649's Homepage  Reply with Quote
no, i dont.



Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/10/2006 :  10:52:59  Show Profile  Visit gongxia649's Homepage  Reply with Quote
can you explain me this condition.
i dont understand it.
on a.street_name like '%' + adt.description + '%'
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/10/2006 :  11:39:23  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i only want to replace the word in the most right?
i'm working on this but still cant figure it out. can someone give me some inputs.


declare @address_type table
(
abr varchar(10),
description varchar(50)
)

declare @address table
(
street_name varchar(50)
)

insert into @address_type
select 'ln', 'lane' union all
select 'rd', 'road' union all
select 'ave', 'avenue'

insert into @address
select 'apple road' union all
select 'orage lane' union all
select 'lane orage lane' union all
select 'apple road road'



select * from @address

update @address
set street_name = replace(a.street_name, adt.description, adt.abr)
from @address a , @address_type adt
where ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) = adt.description



select *from @address
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 08/10/2006 :  12:28:11  Show Profile  Reply with Quote
If you are allergic to hacks, steer clear - this could kill [:-)]


declare @address_type table
(
abr varchar(10),
description varchar(50)
)

declare @address table
(
street_name varchar(50)
)

insert into @address_type
select 'ln', 'lane' union all
select 'rd', 'road' union all
select 'ave', 'avenue'

insert into @address
select 'apple road' union all
select 'orage lane' union all
select 'lane orage lane' union all
select 'apple road road'



select * from @address
select 
	len(a.street_name)+1-charindex(' ', reverse(a.street_name))  as LeftGrabAmount,
	charindex(' ', reverse(a.street_name)) ,
	ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) ,
	substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))),
--	replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr),
	substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))) + replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr)
from @address a
	inner join
	@address_type adt
		on ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) = adt.description

update @address
set street_name = substring(a.street_name ,0,len(a.street_name)+1-charindex(' ', reverse(a.street_name))) + replace(substring(a.street_name,len(a.street_name)+1-charindex(' ', reverse(a.street_name)),charindex(' ', reverse(a.street_name))), adt.description, adt.abr)
from @address a , @address_type adt
where ltrim(reverse(substring(reverse(a.street_name), 1, charindex(' ', reverse(a.street_name))))) = adt.description
select *from @address


results:

street_name
--------------------------------------------------
apple road
orage lane
lane orage lane
apple road road

LeftGrabAmount                                                                                                                   
-------------- ----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6              5           road                                               apple                                              apple rd
6              5           lane                                               orage                                              orage ln
11             5           lane                                               lane orage                                         lane orage ln
11             5           road                                               apple road                                         apple road rd

street_name
--------------------------------------------------
apple rd
orage ln
lane orage ln
apple road rd



*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.15 seconds. Powered By: Snitz Forums 2000