How to compare strings for equality in Transact SQL

I just learned that if a VARCHAR column contains 'ABC ' (three letters
and a space) then
SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
will return the record since SQL Server incorrectly pads the shorter
value with spaces to the length of the longer before comparing them. Is
there a way to compare strings for equality that works correctly?
.Bill.trim?
SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||THECOLUMN like 'ABC'
or
THECOLUMN+'$' = 'ABC'+'$'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||In SQL Server use RTRIM
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
> I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||Grant wrote:

> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
I must be missing something. Using trim gets the same incorrect result
as not using trim. The values 'ABC ' and 'ABC' are NOT equal. If I have
a table with a VARCHAR column that contains two rows with the values
'ABC ' and 'ABC', how do I write a SELECT statement that will return
the row that contains 'ABC' but not the row that contains 'ABC '?
.Bill.|||I think this is the problem he is having:
if 'ABC ' = 'ABC' print 'True' else print 'False'
Result: True
I am assuming he wants this condition to be False. In that base, he actually
doesn't want to trim the spaces.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to be
*different* (in most configurations, SQL Server considers them equal because
the trailing spaces on varchar columns are ignored).
So, using RTRIM() does not help because it yields the same result the OP is
already getting.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||T-SQL has RTRIM() and LTRIM() but no TRIM().
For other info, please see my reply to Jack.
"Grant" <email@.nowhere.com> wrote in message
news:ejQpurxMGHA.3144@.TK2MSFTNGP11.phx.gbl...
> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'|||Aaron Bertrand [SQL Server MVP] wrote:

> Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to
> be different
That is exactly what I want.
.Bill.|||> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them.
Actually, SQL Server is ignoring the trailing spaces, but the result is the
same.
There are a few ways to solve this, JT's solution is pretty simple to
implement...