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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

gongxia649
So Suave

Azores
344 Posts

Posted - 08/04/2006 :  20:17:08  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i like to write a code that street_name field look up in table one for 'road' and replace it for 'rd'.
any approaches ?

table1
abr description
---- ------------
ln lane
rd road
ave avenue


table2
street_name
-------------
apple road

X002548
Not Just a Number

15586 Posts

Posted - 08/04/2006 :  20:20:55  Show Profile  Reply with Quote
Look up REPLACE in BOL

If you want more help read the help link in my sig



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/04/2006 :  20:34:09  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i dont just wanna write the code to replace road.
there are more street abr and more address
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/04/2006 :  21:21:15  Show Profile  Reply with Quote
as Brett suggested, use replace()
update	t2
	set	street_name = replace(street_name, t1.description, t1.abr)
from	table2 t2 inner join table1 t1
on	t2.street_name like '%' + t1.description + '%'



KH

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/05/2006 :  12:12:56  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

i dont just wanna write the code to replace road.
there are more street abr and more address



You will need to give us more info...again, please read the link in my sig



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/05/2006 :  12:21:07  Show Profile  Reply with Quote
I did not know that...I'll have to test it out...but another M$ hack if it's true



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/05/2006 :  19:38:05  Show Profile  Visit gongxia649's Homepage  Reply with Quote
quote:
Originally posted by khtan

as Brett suggested, use replace()
update	t2
	set	street_name = replace(street_name, t1.description, t1.abr)
from	table2 t2 inner join table1 t1
on	t2.street_name like '%' + t1.description + '%'



KH






thanks, but its not working.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/05/2006 :  20:02:54  Show Profile  Reply with Quote
"thanks, but its not working."
Please post the query that you used. It will be very helpful if you could also post the table DDL & same sample data. Read Brett's signature link


KH

Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/05/2006 :  20:13:29  Show Profile  Visit gongxia649's Homepage  Reply with Quote
update address
set street_name = replace(a.street_name, adt.description, adt.abr)
from address a inner join address_type adt
on a.street_name like '%' + adt.description + '%'



address_type
abr description
---- ------------
ln lane
rd road
ave avenue


address
street_name
-------------
apple road
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/05/2006 :  20:59:46  Show Profile  Reply with Quote
Whats wrong with this ?

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'

select	*
from	@address

/* RESULT :

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

*/

update @address
	set street_name = replace(a.street_name, adt.description, adt.abr)
from 	@address a inner join @address_type adt
on 	a.street_name like '%' + adt.description + '%'

select	*
from	@address

/* RESULT :

street_name                                        
-------------------------------------------------- 
apple rd
orage ln

*/



KH

Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/06/2006 :  20:38:46  Show Profile  Visit gongxia649's Homepage  Reply with Quote
when i take out the @ it wont work.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/06/2006 :  21:11:21  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

when i take out the @ it wont work.


Can you explain more ? Any error message ?


KH

Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/06/2006 :  21:13:04  Show Profile  Visit gongxia649's Homepage  Reply with Quote
it says (0 row(s) affected)


i think it doesnt work with permanent tables. i works only with table variables? am i right?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/06/2006 :  21:15:31  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

it says (0 row(s) affected)


i think it doesnt work with permanent tables. i works only with table variables? am i right?



Nope. It will works with permanent table.


KH

Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/06/2006 :  21:18:36  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i used the exact code you wrote. how come it doesnt work.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/06/2006 :  21:19:35  Show Profile  Reply with Quote
Post the script to create your table and insert sample data into the table that we can COPY and PASTE to test out


KH

Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/06/2006 :  21:30:43  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i dunno what happened.
i recreated the tables and it works now. its weird.


thanks for the help again.

Edited by - gongxia649 on 08/06/2006 21:31:09
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/06/2006 :  22:05:07  Show Profile  Reply with Quote
Yeah weird !


KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/07/2006 :  08:55:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by gongxia649

i dunno what happened.
i recreated the tables and it works now. its weird.


thanks for the help again.


May be the data is invalid

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/07/2006 :  08:55:39  Show Profile  Visit funketekun's Homepage  Reply with Quote
maybe.

but anyway its fixed..thanks guys for your help.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/07/2006 :  09:08:56  Show Profile  Reply with Quote
gongxia649 and funketekun is the same person ?


KH

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.17 seconds. Powered By: Snitz Forums 2000