search & replace - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

search & replace

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-04 : 20:17:08

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 - 2006-08-04 : 20:20:55

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

gongxia649
So Suave

344 Posts

Posted - 2006-08-04 : 20:34:09

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 21:21:15

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 + '%'

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-05 : 12:12:56

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

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-05 : 12:21:07

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

gongxia649
So Suave

344 Posts

Posted - 2006-08-05 : 19:38:05

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 + '%'

thanks, but its not working.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-05 : 20:02:54

"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

gongxia649
So Suave

344 Posts

Posted - 2006-08-05 : 20:13:29

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-05 : 20:59:46

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

*/

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 20:38:46

when i take out the @ it wont work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-06 : 21:11:21

quote:
Originally posted by gongxia649

when i take out the @ it wont work.

Can you explain more ? Any error message ?

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 21:13:04

it says (0 row(s) affected)

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-06 : 21:15:31

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.

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 21:18:36

i used the exact code you wrote. how come it doesnt work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-06 : 21:19:35

Post the script to create your table and insert sample data into the table that we can COPY and PASTE to test out

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 21:30:43