CODEWITHSUNDEEP
sql-servertype-conversion
ArthurTheLearner
ArthurTheLearner

Reputation: 315

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

In SQL Server, the following query

SELECT CONVERT(VARBINARY,'1234');

returns 0x31323334.

What can I do in the query in order to return 0x3100320033003400?

So far, I can do this in C++ and then use it in queries, but I was wondering if there is a cleaner way of doing this (i.e. a way of putting the original string inside the query and letting SQL do the work).

Maybe there is a way of doing the conversion telling SQL that each character in '1234' must be treated as a two byte character. Maybe there is a way splitting and joining. I can't find the way and these are the two approaches I've tried so far.

Upvotes: 0

Views: 1113

Answers (1)

Thom A
Thom A

Reputation: 95830

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).

Upvotes: 2

Related Questions