String manipulation - 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

String manipulation

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-16 : 11:14:36

create table #temp
(
route varchar(50)
)

insert into #temp
select 'AMS-BRU-LON--' union all
select 'LON-AMS---' union all
select 'HOW-CTK-VSK-SEC-'

Expected Result:

AMS-BRU-LON
LON-AMS
HOW-CTK-VSK-SEC

Need to remove all right '-' before Alphabet

Please help me

--Ranjit

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-16 : 11:36:07

Here's one way - you can change the SELECT into an UPDATE if you want to overwrite the original values


select left([route], len([route]) - patindex('%[a-z]%', reverse([route]))+1)
from   #temp

Be One with the Optimizer
TG

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-05-16 : 21:02:01

A less fancy but equally effective answer:
select replace(rtrim(replace(route,'-',' ')),' ','-')
from #temp

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-17 : 00:28:27

Thank u both for your replys

--Ranjit

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-17 : 10:39:09

quote:
Originally posted by pduffin

A less fancy but equally effective answer:
select replace(rtrim(replace(route,'-',' ')),' ','-')
from #temp

nice solution. But, Ranjit, just make sure your [route] values don't contain legitimate spaces if you go with this one...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources