replace roa - 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 roa

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-11-15 : 15:02:31

i just wanna replace the last word 'roa' to 'road'
what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.

declare @table table(ad_str1 varchar(20))
insert @table
select 'street road roa' union all
select 'street street'

select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table

update @table
set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road')
where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'

select * from @table

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 15:09:57

If that's really all you want, then this will do it.

declare @table table(ad_str1 varchar(20))
insert @table 
select 'street road roa' union all
select 'street street'

update @table
set ad_str1 = ad_str1 + 'd'
where right(ad_str1, 4) = ' roa'

select * from @table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 15:11:22

Are you still fighting with this project?

update @table
set ad_str1 = ad_str1 + 'd'
where ad_str1 like '%roa'

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-15 : 15:11:28

REPLACE(ad_str1,'road roa','road')

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gongxia649
So Suave

344 Posts

Posted - 2006-11-15 : 15:12:07

i also want to know why my code doesnt work.

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-15 : 15:16:44

PEBKAC?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-15 : 15:25:08

ouch brett, ouch!

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

gongxia649
So Suave

344 Posts

Posted - 2006-11-15 : 15:29:15

so anyone know why my code is not working?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 15:42:23

quote:
Originally posted by gongxia649

so anyone know why my code is not working?

Because it says replace all occurrences of 'roa' with 'road'.

gongxia649
So Suave

344 Posts

Posted - 2006-11-15 : 16:33:50

quote:
Originally posted by snSQL

quote:
Originally posted by gongxia649

so anyone know why my code is not working?

Because it says replace all occurrences of 'roa' with 'road'.

oh. yeah i can see that now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources