SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 replace() question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gongxia649
So Suave

Azores
344 Posts

Posted - 09/26/2006 :  16:19:56  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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  Show Profile  Reply with Quote
It would be helpful if you explained what you want to do.



CODO ERGO SUM
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 09/26/2006 :  17:46:44  Show Profile  Visit timmy's Homepage  Reply with Quote
SELECT apple
FROM tree
WHERE branch_height < 2 AND ripeness >= 80%


Edited by - timmy on 09/26/2006 17:47:33
Go to Top of Page

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 09/26/2006 :  19:30:21  Show Profile  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/26/2006 :  20:58:52  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 09/27/2006 :  00:52:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Add every known variation of apple to the @lookup table, such as

insert @lookup values(1, 'appple', 'text')


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 09/27/2006 :  13:39:32  Show Profile  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 10/18/2006 :  09:14:56  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/18/2006 :  09:21:53  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
SELECT stuff(ad_str1, len(ad_str1), 1, 'internet') from @table


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 10/18/2006 :  09:25:17  Show Profile  Visit gongxia649's Homepage  Reply with Quote
harsh athalye,
the strings next to s,w,n,e are avenue, road, circle and crossing.
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/18/2006 :  10:21:06  Show Profile  Send jen a Yahoo! Message  Reply with Quote
provide sample data and expected result


--------------------
keeping it simple...
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 10/18/2006 :  10:27:26  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  16:13:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000