2

When retrieving a text value, which has been stored in a varbinary field, and converting back to text, is there a way to determine whether it was stored as a VARCHAR or NVARCHAR string? If it is unknown, telling the CONVERT function it is a NVARCHAR string, when it is in fact VARCHAR will return gibberish. Run the below for an example:

IF OBJECT_ID(N'dbo.Temp') IS NOT NULL
  DROP TABLE dbo.Temp;

CREATE TABLE Temp(
   ID INT IDENTITY(0,1) NOT NULL
  ,Test VARBINARY(50) NULL 
);

INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), 'varchar test string'));
INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), N'nvarchar test string'));

SELECT CONVERT(VARCHAR, Test) AS [VARCHAR], CONVERT(NVARCHAR, Test) AS [NVARCHAR]
FROM dbo.Temp;
3
  • What is the reason for the requirement? Why wouldn't they just be stored as strings? For varchar converting from binary can return different results under different collations too. Aug 27, 2013 at 13:18
  • 1
  • @Martin, The background for the question is related to encryption. There is a scenario where I think different value types may have been inserted using EncryptByKey and when I DecryptByKey is doesn't always return correctly depending on which type is used.
    – Delux
    Aug 27, 2013 at 13:40

1 Answer 1

4

This isn't really possible in general.

The full range from 0x00 to 0xFF is valid for varchar and from 0x0000 to 0xFFFF for nvarchar so it is impossible to distinguish between two bytes that represent 2 varchar characters or a single nvarchar character.

0x4141 could be 'AA' or N'䅁'

If DATALENGTH is odd then you know it is varchar though.

Also if the text is primarily standard ASCII as in your example then it will contain a lot of 0x00 when stored as binary if nvarchar.

This is probably not a character you expect to reasonably find at all in varchar strings so the presence of that could be taken as evidence that the string is nvarchar.

3
  • It was also just recommended to me that if different types are going to be encrypted to the binary field, that I should have an additional column to indicate what type was encrypted.
    – Delux
    Aug 27, 2013 at 13:51
  • Didn't I also read that Microsoft doesn't guarantee that conversions from any other datatype to binary and back will be consistent between SQL versions? Aug 27, 2013 at 14:06
  • @KennethFisher - Yes. This is stated in binary and varbinary. The section on DECRYPTBYKEY does give an example with nvarchar though... Aug 27, 2013 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.