Search code examples
sql-servert-sqlsql-server-2016varbinary

Issue when converting from NVARCHAR to VARBINARY


When converting varchar to varbinary, I can revert it back. For example:

SELECT CAST('_A_Brand1[_1]A5_LP[_1].A5' AS VARBINARY)
      ,CAST('_A_Brand1[_1]A5_LP[_2].A5' AS VARBINARY);

gives two different outputs:

0x5F415F4272616E64315B5F315D41355F4C505B5F315D2E4135 0x5F415F4272616E64315B5F315D41355F4C505B5F325D2E4135

but if I use nvarchar I get the same output:

SELECT CAST(N'_A_Brand1[_1]A5_LP[_1].A5' AS VARBINARY)
      ,CAST(N'_A_Brand1[_1]A5_LP[_2].A5' AS VARBINARY);

0x5F0041005F004200720061006E00640031005B005F0031005D0041003500 0x5F0041005F004200720061006E00640031005B005F0031005D0041003500

Which is an issue, because I can not perform correct operations later:

SELECT CAST(CAST('_A_Brand1[_1]A5_LP[_1].A5' AS VARBINARY) AS VARCHAR(MAX))
      ,CAST(CAST('_A_Brand1[_1]A5_LP[_2].A5' AS VARBINARY) AS VARCHAR(MAX));

SELECT CAST(CAST(N'_A_Brand1[_1]A5_LP[_1].A5' AS VARBINARY) AS NVARCHAR(MAX))
      ,CAST(CAST(N'_A_Brand1[_1]A5_LP[_2].A5' AS VARBINARY) AS NVARCHAR(MAX));

The first is OK:

_A_Brand1[_1]A5_LP[_1].A5
_A_Brand1[_1]A5_LP[_2].A5

The second returns same strings (value is lost/cut):

_A_Brand1[_1]A5
_A_Brand1[_1]A5

Solution

  • Default length of VARBINARY is 30, that's why data is truncated.

    Corrected code:

    SELECT CAST(CAST(N'_A_Brand1[_1]A5_LP[_1].A5' AS VARBINARY(MAX)) AS NVARCHAR(MAX))
          ,CAST(CAST(N'_A_Brand1[_1]A5_LP[_2].A5' AS VARBINARY(MAX)) AS NVARCHAR(MAX));