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
Go to Top of Page

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
Go to Top of Page

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



KH

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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



KH






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

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


KH

Go to Top of Page

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
Go to Top of Page

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

*/



KH

Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 20:38:46
when i take out the @ it wont work.
Go to Top of Page

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 ?


KH

Go to Top of Page

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?
Go to Top of Page

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.


KH

Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 21:18:36
i used the exact code you wrote. how come it doesnt work.

Go to Top of Page

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


KH

Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-08-06 : 21:30:43
i dunno what happened.
i recreated the tables and it works now. its weird.


thanks for the help again.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-06 : 22:05:07
Yeah weird !


KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-07 : 08:55:06
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

491 Posts

Posted - 2006-08-07 : 08:55:39
maybe.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 09:08:56
gongxia649 and funketekun is the same person ?


KH

Go to Top of Page
    Next Page

- Advertisement -