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
 search & replace
 New Topic  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
1167 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
 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.14 seconds. Powered By: Snitz Forums 2000