| 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 |
|
|
|
replace() question