SQL Server string truncation on Convert and Cast

This one will also be short. We came on this issue today: our code is decrypting some fields in the SQL Server database and as a part of decryption we are using Convert. So, the code looks like this:

SELECT CONVERT(varchar, DecryptByKey( fieldName ) ) AS decryptedField

What is the issue? The field contents are truncated to 30 chars! Initially, I was thinking that this is caused by Encryption or Decryption of the field, but I ran the following test:

SELECT CONVERT(varchar, '01234567890123456789012345678901234567890123456789012345678901234567890123456789')
SELECT CAST('01234567890123456789012345678901234567890123456789012345678901234567890123456789' AS varchar)

And as a result for both I got:

012345678901234567890123456789

Exactly 30 chars! Why? Because the default data length for varchar is 30 chars. This affects Convert and Cast functions. For both of them, if you don’t declare the length of the varchar, it will give you the first 30 chars. The solution for our initial issue was simple:

SELECT CONVERT(varchar(512), DecryptByKey( fieldName ) ) AS decryptedField

Leave a Reply

Your email address will not be published. Required fields are marked *