| Author |
 Topic  |
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/04/2006 : 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 |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/04/2006 : 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)
Singapore
16746 Posts |
Posted - 08/04/2006 : 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
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/05/2006 : 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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/05/2006 : 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
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/05/2006 : 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)
Singapore
16746 Posts |
Posted - 08/05/2006 : 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
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/06/2006 : 20:38:46
|
| when i take out the @ it wont work. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/06/2006 : 21:11:21
|
quote:
Originally posted by gongxia649
when i take out the @ it wont work.
Can you explain more ? Any error message ?
KH
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/06/2006 : 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)
Singapore
16746 Posts |
Posted - 08/06/2006 : 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
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/06/2006 : 21:18:36
|
i used the exact code you wrote. how come it doesnt work.
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/06/2006 : 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
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/06/2006 : 21:30:43
|
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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/06/2006 : 22:05:07
|
Yeah weird !
KH
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/07/2006 : 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 |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 08/07/2006 : 08:55:39
|
maybe.
but anyway its fixed..thanks guys for your help. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/07/2006 : 09:08:56
|
gongxia649 and funketekun is the same person ?
KH
|
|
|
|
Topic |
|
search & replace
