Remove Last Some Characters from TSQL Variable

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 2005 Forums

Transact-SQL (2005)

Remove Last Some Characters from TSQL Variable

Author

Topic

shahid00704
Starting Member

11 Posts

Posted - 2009-12-16 : 10:58:26

Hello All...
I have some thing like this in my tsql query

if @RegionID<>0
Begin
SET @Where=@Where+' RegionID'=+str(@RegionID)+' And'
End
if @TelecomVendorID<>0
Begin
SET @Where=@Where+' RentalCompanyID'+Str(@TelecomVendorID)
End

I want to remove the last +' And' if there is zero for @TelecomVendorID.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-16 : 11:33:23

A simple way but im sure not the best.

Declare @mystring nvarchar(255)
set @mystring = 'this is my string and'

Select @mystring

Set @mystring = Substring(@mystring,1, len(@mystring)-3)
Select @mystring

You can even do -4 if you want to get rid of the space.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-16 : 12:22:57

/*if @RegionID<>0
Begin
SET @Where=@Where+' RegionID'=+str(@RegionID)+' And'
End
if @TelecomVendorID<>0
Begin
SET @Where=@Where+' RentalCompanyID'+Str(@TelecomVendorID)
End

*/

DECLARE @TelecomVendorID int
DECLARE @Where varchar(100)
DECLARE @RegionID int

SET @RegionID = 12
SET @Where= ' RegionID = ' +convert(varchar(3), @RegionID)+' And'
SET @TelecomVendorID = 7

SET @where = CASE WHEN @TelecomVendorID = 0
THEN REPLACE(@where,' AND','')
ELSE @Where +' RentalID = ' + convert(varchar(3),(@TelecomVendorID) )
END

select @where

Jim

Everyday I learn something that somebody else already knew

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-16 : 13:06:09

I was afraid of REPLACE(@where,' AND','') just incase that was a snippet from more code, if he has more If's prior to the @RegionID and they were joining more 'and' s you run the risk of replacing them all, when all he wants is the last AND removed. or lets hypothesis @RegionID 'could' be Alpha Numeric, if @RegionID = 'RANDOLF001' then the RegionID would be comprimised ('ROLF001').

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 13:35:33

An alternative approach...

if @RegionID<>0
Begin
SET @Where=@Where+' RegionID'=+str(@RegionID)+' And'
End
if @TelecomVendorID<>0
Begin
SET @Where=@Where+' RentalCompanyID'+Str(@TelecomVendorID)+' And'
End
set @Where=@Where+' 1=1'

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-16 : 14:21:32

Ahh I like that solution. Clever.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources