Is it possible to store binary data as varchar/nvarchar in SQL servers tables?

Example I am trying to store binary data 0x6806000004000000 as nvarchar data type but while trying to save to the tables values are getting blanked out. I want them to be stored as strings

Code used

declare @tempBinayToVarchar table (FinalValue NVARCHAR(60))
Insert into @tempBinayToVarchar
select Value from basetable 

Values in the table are values like:

0x000000000000000000000000000000000000000000000000000000000000004

CodePudding user response：

TL;DR: Store your binary data in a varbinary and if you "must" store it in a (n)varchar data type, don't expect readable information.

The value isn't being blanked out. As I mentioned in the comments, the value 0x00 as a varchar (0x0000 for an nvarchar) is a null character; applications can't display this character and many actually parse it as the end of the string.

That, however, doesn't mean the value is gone, it just can't be displayed. You can see this with the following SQL:

CREATE TABLE dbo.SomeTable (BinaryString varchar(100),
                            ActualBinary AS CONVERT(varbinary(100), BinaryString));
GO

INSERT INTO dbo.SomeTable (BinaryString)
VALUES(0x6806000004000000),
      (0x006806000004000000);
GO

SELECT *
FROM dbo.SomeTable;
GO

DROP TABLE dbo.SomeTable;

Note that the values in the column ActualBinary are the binary values that are inserted. In your application though, you may get nothing. In ADS you get the value prefixed with a h for both rows, but in SSMS you get nothing for the row starting with a null character:

ADS:

SSMS:

CodePudding user response：

If you're trying to just store the 0x000... as a string without converting it to what the binary value actually represents, you can do that using CONVERT(0x000..., 1) though, for the specific value in your question, you need more than 60 characters.

DECLARE @tempBinayToNvarchar table (FinalValue nvarchar(120));

INSERT @tempBinayToNvarchar(FinalValue)
  SELECT CONVERT(nvarchar(120), 
    0x000000000000000000000000000000000000000000000000000000000000004, 
  1);
  
SELECT FinalValue FROM @tempBinayToNvarchar;

Output:

• Example db<>fiddle