SQL: Selecting from a varbinary column ignores trailing zeros

Question

I have a varbinary(16) column in a MSSQL database.

One of the possible contents of this column is a PIN. A PIN can have a variable number of digits: "1234" or "12340" for example.

In the PIN case, every digit is represented as a single byte, so these PINs would be stored as "0x01020304" and "0x0102030400".

My problem now is the following: I insert the PIN "12340" and then try to find the PIN "1234" and it will return the row for "12340".

SELECT * FROM Token WHERE TokenId = @bytes

@bytes is a byte[4]{1,2,3,4} using Dapper.

I would not expect that the row with the trailing zeros will be found. I think that is because of the ANSI_PADDING set to ON, but it should always be on by MSDN.

I think one possibility would be to check the DATALENGTH which would return different length for these two PINs (4 and 5).

But is that best practice? Or is there a better solution?

Thanks in advance!

---

EDIT:

Insert (simplified):

string insertSql = "INSERT INTO Token (TokenId) VALUES (@Bytes)";
connection.Execute( insertSql, new { Bytes = new byte[]{1,2,3,4,0} } );

Select (simplified):

// This returns the inserted row above
string selectSql = "SELECT * FROM Token WHERE TokenId = @Bytes";
connection.Query<TokenRow>( selectSql, new { Bytes = new byte[]{1,2,3,4} } );

Answer 1

It depends on what is the datatype of the data to be cast...

Demo :

SELECT CAST(1234 AS VARBINARY(16)),
       CAST(12340 AS VARBINARY(16));

------------------------ ------------------------
0x000004D2               0x00003034

SELECT CAST('1234' AS VARBINARY(16)),
       CAST('12340' AS VARBINARY(16));

------------------------ ------------------------
0x31323334               0x3132333430

Post the complete SQL code