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 ?table1abr description---- ------------ln lane rd road ave avenue table2street_name-------------apple road |
|
X002548
Not Just a Number
15586 Posts |
|
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 t1on t2.street_name like '%' + t1.description + '%' KH |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
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 t1on t2.street_name like '%' + t1.description + '%' KH
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 KH |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-05 : 20:13:29
|
update addressset street_name = replace(a.street_name, adt.description, adt.abr)from address a inner join address_type adton a.street_name like '%' + adt.description + '%'address_typeabr description---- ------------ln lane rd road ave avenue addressstreet_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_typeselect 'ln', 'lane' union allselect 'rd', 'road' union allselect 'ave', 'avenue'insert into @addressselect 'apple road' union allselect 'orage lane'select *from @address/* RESULT :street_name -------------------------------------------------- apple roadorage lane*/update @address set street_name = replace(a.street_name, adt.description, adt.abr)from @address a inner join @address_type adton a.street_name like '%' + adt.description + '%'select *from @address/* RESULT :street_name -------------------------------------------------- apple rdorage ln*/ KH |
|
|
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 ? KH |
|
|
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. KH |
|
|
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 KH |
|
|
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. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-06 : 22:05:07
|
Yeah weird ! KH |
|
|
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 invalidMadhivananFailing to plan is Planning to fail |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-08-07 : 08:55:39
|
maybe.but anyway its fixed..thanks guys for your help. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-07 : 09:08:56
|
gongxia649 and funketekun is the same person ? KH |
|
|
Next Page
|