trim off last AND - 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

SQL Server 2008 Forums

Transact-SQL (2008)

trim off last AND

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-07-06 : 08:28:38

Hi,
How do I trim off the 'AND' if it is present at the end of the line?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-06 : 08:33:58

declare @line varchar(255)
set @line = 'bla and bla and bla and'
select
reverse(stuff(reverse(@line),1,4,''))

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-07-06 : 08:58:20

But what if there is no 'AND'
The sql should know if there is or there is not an AND at the end of the line.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-06 : 09:00:39

good point:

declare @line varchar(255)
set @line = 'bla and bla and bla and'
select
case
when right(@line,4)=' and' then reverse(stuff(reverse(@line),1,4,''))
else @line
end

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-06 : 09:05:01

or this:
reverse(stuff(reverse(@line),1,case when right(@line,4)=' and' then 4 else 0 end,''))

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-07-06 : 09:47:45

quote:
Originally posted by webfred

good point:

declare @line varchar(255)
set @line = 'bla and bla and bla and'
select
case
when right(@line,4)=' and' then reverse(stuff(reverse(@line),1,4,''))
else @line
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources