Reputation: 84
Why does the following return a result when the target column in the where clause is a char(20)?
declare @pallettag varchar(20)
set @pallettag = '168531'
--set @pallettag = @pallettag + SPACE(20 - Len(@pallettag))
select s.ROWID, s.SUBLOTCODE + '*', @pallettag + '*'
from IMSUBLOT as s
where s.SUBLOTCODE = @pallettag
s.SUBLOTCODE is defined as char(20) and I would expect to get a result only if I uncomment the third line where I added the needed spaces.
Upvotes: 5
Views: 6592
Reputation: 454020
Trailing spaces are ignored in string comparisons in SQL Server except for expressions on the right in a LIKE
comparison.
SELECT CASE
WHEN '168531' = '168531 ' THEN 'Y'
ELSE 'N'
END AS [=],
CASE
WHEN '168531' LIKE '168531 ' THEN 'Y'
ELSE 'N'
END AS [Like RHS],
CASE
WHEN '168531 ' LIKE '168531' THEN 'Y'
ELSE 'N'
END AS [Like LHS]
Returns
= Like RHS Like LHS
---- -------- --------
Y N Y
Upvotes: 6