replace() question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

replace() question

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-09-26 : 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.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-26 : 16:43:46

It would be helpful if you explained what you want to do.

CODO ERGO SUM

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-26 : 17:46:44

[code]SELECT apple
FROM tree
WHERE branch_height < 2 AND ripeness >= 80%[/code]

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-26 : 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

344 Posts

Posted - 2006-09-26 : 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 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 - 2006-09-27 : 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

344 Posts

Posted - 2006-10-18 : 09:14:56

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-18 : 09:21:53

[code]SELECT stuff(ad_str1, len(ad_str1), 1, 'internet') from @table[/code]

Harsh Athalye
India.
"Nothing is Impossible"

gongxia649
So Suave

344 Posts

Posted - 2006-10-18 : 09:25:17

harsh athalye,
the strings next to s,w,n,e are avenue, road, circle and crossing.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-18 : 10:21:06

provide sample data and expected result

--------------------
keeping it simple...

gongxia649
So Suave

344 Posts

Posted - 2006-10-18 : 10:27:26

jen,
I just want to select the word next to w, e,n,s

road, avenue, circle, crossing.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources