Jon Reopelle
Jon Reopelle

Reputation: 84

SQL char to varchar comparison works, why?

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

Answers (2)

Martin Smith
Martin Smith

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

Maess
Maess

Reputation: 4166

VARCHAR(20) means any length of characters up to and including 20.

Upvotes: 0

Related Questions