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
Fillfactor for a Sequential Index That Is Pk
Difference Between <> and != in Sql
How to Open Multiple .Sql Files in Only One Ssms Instance
Postgres on Conflict Do Update on Composite Primary Keys
Is There a Tool to Generate a Full Database Ddl for SQL Server? What About Postgres and MySQL
How to Have Temp Tables in a Function
Best Way to Find SQL Locks in SQL Server 2008
Sql Select Return Default Value If Null
Sql 2005 How to Use Keyword Like in a Case Statement
How to Force a Column to Be Unique for an Entire Table in SQL Server 2008 R2
Using Where Clause with Between and Null Date Parameters
Understanding Bitmap Indexes in Postgresql
Querying Multiple Tables in Big Query
Delete Duplicate Records Using Rownum in Sql
Data Type Equivalents: Ms Access Tables ↔ 'Create Table' Queries ↔ Odbc Sql
How to Deal with Spark Udf Input/Output of Primitive Nullable Type