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

Transact-SQL (2000)

trim off last AND

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-02-21 : 03:47:06

Hi,
How do you trim off the last AND in a sql string please?

for example you have:
select * from table1 where field1=2 AND

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-21 : 04:14:14

Hi,
Are you trying to build your Sql statement dynamically.
Either put default equals statement at the end
i.e 1=1

to remove last AND try this..

@sqlstring = 'select * from table1 where field1=2 AND'
select @sqlstring = left(@sqlstring,len(ltrim(rtrim(@sqlstring))) - 3 )

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-02-21 : 04:25:46

Hello again,
What if there is no AND at the end of the sql
so you may have:
select * from Table1
or
select * from table1 where field1=1
or
select * from table1 where field1=1 and field2=2 AND

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-21 : 04:31:29

Hi,
Put a Case statement and check right 3 charatcers if its AND then remove else NOT

set @sqlstring = 'select * from table1 where field1=2 AND'
or
set @sqlstring = 'select * from Table1'
or
set @sqlstring = 'select * from table1 where field1=1'

select @sqlstring = case right(ltrim(rtrim(@sqlstring )), 3) when 'AND' then left(@sqlstring,len(ltrim(rtrim(@sqlstring))) - 3 )
else
@sqlstring
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-21 : 05:35:51

Post the full code you are using

Other method would be

select @sqlstring = substring(@sqlstring,1,len(ltrim(rtrim(@sqlstring))) - 3 )
where @sqlstring like '% AND'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources