Author |
Topic  |
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/26/2006 : 16:19:56
|
declare @table table (ad_str1 varchar(50)) insert @table select 'apple avenue w' union all select 'melon road e' union all select 'watermelon circle n'union all select 'banana durian crossing s'
select * from @table declare @temp varchar(20) select @temp = substring((ad_str1),charindex(' ',(ad_str1),1)+1,len((ad_str1))) from @table print(@temp)
update @table set @temp = replace(@temp, substring(@temp,1,charindex(' ',@temp,1)-1), 'internet') where ltrim(reverse(substring(reverse(ad_str1), 1, charindex(' ', reverse(ad_str1))))) in ('w','e','s','n')
select @temp = substring(@temp,1,charindex(' ',@temp,1)-1) print (@temp) select * from @table -------------------------------------------------------------------------------------------------------------------------
Im trying to replace the string next to S,W,N,E to 'internet'. But seems like it's not working. Any input will be appreaciated. |
Edited by - gongxia649 on 10/18/2006 09:54:10
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 09/26/2006 : 16:43:46
|
It would be helpful if you explained what you want to do.
CODO ERGO SUM |
 |
|
timmy
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 09/26/2006 : 17:46:44
|
SELECT apple
FROM tree
WHERE branch_height < 2 AND ripeness >= 80%
 |
Edited by - timmy on 09/26/2006 17:47:33 |
 |
|
Gopi Nath Muluka
Starting Member
22 Posts |
Posted - 09/26/2006 : 19:30:21
|
Not Clear on your requirement, look at this query It will fetch what you want, but Can you explain me , what are you trying to do with pattern match in your query
select distinct a.id_town, a.ad_str1 from @table2 a join @lookup l on a.id_town = l.id_town and substring(a.ad_str1, 1, len(a.ad_str1) - charindex(' ', reverse(a.ad_str1))) not in (select ad_str1 from @lookup)
Thanks, Gopi Nath Muluka |
 |
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/26/2006 : 20:58:52
|
gopi Nath muluka, i want to strip the predirection and and street abbreviation or last word. like if you have "E appple st". I want to get "apple" then check if this string exists in @lookup table. If it doesn't exists in this @lookup table then retrieve it.
Predirection have 4 ways: e, s, n, w.
|
Edited by - gongxia649 on 09/26/2006 21:00:55 |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 09/27/2006 : 00:52:16
|
Add every known variation of apple to the @lookup table, such as
insert @lookup values(1, 'appple', 'text')
Peter Larsson Helsingborg, Sweden |
 |
|
Gopi Nath Muluka
Starting Member
22 Posts |
Posted - 09/27/2006 : 13:39:32
|
I think this is what you are looking for
select distinct a.id_town,a.ad_str1 from @table2 a join @lookup l on a.id_town = l.id_town and Replace(case left(a.ad_str1,2) When 'E ' then right(a.ad_str1,len(a.ad_str1)-2) When 'W ' then right(a.ad_str1,len(a.ad_str1)-2) When 'N ' then right(a.ad_str1,len(a.ad_str1)-2) When 'S ' then right(a.ad_str1,len(a.ad_str1)-2) Else a.ad_str1 End, ' st' ,'') not in (select ad_str1 from @lookup)
Thanks, Gopi Nath Muluka |
 |
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 10/18/2006 : 09:14:56
|
declare @table table (ad_str1 varchar(50)) insert @table select 'apple avenue w' union all select 'melon road e' union all select 'watermelon circle n'union all select 'banana durian crossing s'
select * from @table declare @temp varchar(20) select @temp = substring((ad_str1),charindex(' ',(ad_str1),1)+1,len((ad_str1))) from @table print(@temp)
update @table set @temp = replace(@temp, substring(@temp,1,charindex(' ',@temp,1)-1), 'internet') where ltrim(reverse(substring(reverse(ad_str1), 1, charindex(' ', reverse(ad_str1))))) in ('w','e','s','n') select @temp = substring(@temp,1,charindex(' ',@temp,1)-1) print (@temp) select * from @table -------------------------------------------------------------------------------------------------------------------------
Im trying to replace the string next to S,W,N,E to 'internet'. But seems like it's not working. Any input will be appreaciated.
|
Edited by - gongxia649 on 10/18/2006 09:26:43 |
 |
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 10/18/2006 : 09:21:53
|
SELECT stuff(ad_str1, len(ad_str1), 1, 'internet') from @table
Harsh Athalye India. "Nothing is Impossible" |
 |
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 10/18/2006 : 09:25:17
|
harsh athalye, the strings next to s,w,n,e are avenue, road, circle and crossing. |
 |
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 10/18/2006 : 10:21:06
|
provide sample data and expected result
-------------------- keeping it simple... |
 |
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 10/18/2006 : 10:27:26
|
jen, I just want to select the word next to w, e,n,s
road, avenue, circle, crossing. |
Edited by - gongxia649 on 10/18/2006 15:11:23 |
 |
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 16:13:59
|
How about this
declare @table table (ad_str1 varchar(50))
insert @table
select 'apple avenue w' union all
select 'melon road e' union all
select 'watermelon circle n'union all
select 'banana durian crossing s'
select * from @table
update @table set ad_str1 =
replace(ad_str1, reverse(substring(reverse(ad_str1), 3, charindex(' ', reverse(ad_str1), 3) - 3)), 'internet')
where left(reverse(ad_str1), 2) in ('n ', 's ', 'e ', 'w ')
select * from @table
|
 |
|
|
Topic  |
|
|
|