Sql Server String to Varbinary Conversion

why is CONVERT string to VARBINARY in SQL Server only converting first character?

The reason is that when you insert you're converting a Unicode (nvarchar(xx)) string to varbinary. Then when you select you're converting to varchar(xx). If you convert to nvarchar(xx) it will work fine.

For example:

  • inserting 'this is a test' as varbinary(30) results in 0x7468697320697320612074657374.

  • inserting N'this is a test' as varbinary(30) results in 0x74006800690073002000690073002000610020007400650073007400.

So when you convert back, if you specify varchar(30) the first 00 will truncate the string.

This works fine for me:

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') )
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2

and so does this

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), 'this is a test') )
select * from Table_2
select CONVERT(varchar(30), test) from Table_2

Convert string representation of VARBINARY to VARBINARY value

So, generally speaking you will use convert to go to and from varbinary and varchar. You'll want to use 2 for the style instead of 1 to get the string representation. If you want the literal conversion, use the default of 0.

DB FIDDLE

declare @v varbinary(128) = (select cast('ThisIsMyPassword' as varbinary(128)))

select
@v as Val
,convert(varchar(max),@v,2) as String
,convert(varchar(max),@v,0) as Literal

See the docs on when to use the styles here

How to convert varchar to varbinary where each char is followed by a null character?

Convert it to an nvarchar and then a varbinary, as an nvarchar uses double bytes to store characters:

SELECT CONVERT(VARBINARY(8),CONVERT(nvarchar(4),'1234'));

Or, if it's always a literal, just prefix it with an nvarchar notation character (N).

SQL Server string to varbinary conversion

Ok, so the padded 00 has been answered.

DECLARE @hexStringNVar nvarchar(max)
DECLARE @hexStringVAR varchar(max)

SET @hexStringNVar = '{my hex string as described above}'
SET @hexStringVAR = '{my hex string as described above}'

select CONVERT(varbinary(MAX), @hexStringNVar)) = 0x6100700070006C00690063...
select CONVERT(varbinary(MAX), @hexStringVAR)) = 0x6170706C6963...

The 00 padding is because of Unicode or NVARCHAR as opposed to VARCHAR.

So, since the stored data is in nvarchar(max), the solution is this:

select CAST(cast(@hexStringNVar as varchar(max)) as varbinary(max)) = 0x6170706C6963...

I'm sure that convert would work just as well but my target SQL Server is 2005.

SQL string to varbinary through XML different than nvarchar

Using the value() function to get a XML value specified as varbinary(max) will read the data as if it was Base64 encoded. Casting a string to varbinary(max) does not, it treats it as just any string.

If you use the input string QQA= which is the letter A in UTF-16 LE encoded to Base64 you will see more clearly what is happening.

XML gives you 0x4100, the varbinary of the letter A, and direct cast on the string gives you 0x5100510041003D00 where you have two 5100 = "Q" and of course one 4100 = "A" followed by a 3D00 = "="

Error converting VarChar to VarBinary in SQL Server

Hmm, AFEE27AF97DC6 is one nibble short and it seems like only full bytes are accepted. Try to zero pad it. E.g.

SELECT convert(varbinary(max), '0AFEE27AF97DC6', 2)

You can also wrap it in a CASE expression checking if the string has an even or odd length, should the strings be variable.

SELECT convert(varbinary(max),
CASE
WHEN len('AFEE27AF97DC6') % 2 <> 0 THEN
concat('0', 'AFEE27AF97DC6')
ELSE
'AFEE27AF97DC6'
END,
2)

(Replace the literals with your variable.)



Related Topics



Leave a reply



Submit