I have a 16-bit decimal value in SQL that represents one or two ASCII characters.

For example, 16705 (0x4141 in HEX) is AA.

I need to convert these values to get the characters as output. I found an answer that got me close:

SELECT convert(varbinary(MAX), 16705) gives me 0x00004141

From here I can (almost) process the result one more time:

SELECT convert(VARCHAR(MAX), 0x4141) to get AA.

This is the the string I need. The problem is I have the extra padded 0's in the middle of the first result, and I can't figure out how to deal with them. Taking the binary 0x00004141 value directly gives me nothing:

SELECT convert(VARCHAR(MAX), 0x00004141)

Is there something I can do to handle the extra 0s, or is there a more straightforward way to do this conversion?

I have a 16-bit decimal value in SQL that represents one or two ASCII characters.

For example, 16705 (0x4141 in HEX) is AA.

I need to convert these values to get the characters as output. I found an answer that got me close:

SELECT convert(varbinary(MAX), 16705) gives me 0x00004141

From here I can (almost) process the result one more time:

SELECT convert(VARCHAR(MAX), 0x4141) to get AA.

This is the the string I need. The problem is I have the extra padded 0's in the middle of the first result, and I can't figure out how to deal with them. Taking the binary 0x00004141 value directly gives me nothing:

SELECT convert(VARCHAR(MAX), 0x00004141)

Is there something I can do to handle the extra 0s, or is there a more straightforward way to do this conversion?

• sql
• sql-server
• type-conversion

1 Answer 1

To understand what is happening with the 0x00004141, it's worth talking about strings for a moment. One common way strings are represented in memory is as an array of characters, where the last character in the string is a special "null terminator". The null terminator has a binary value of 0 and indicates the end of the string, regardless of the size of the array.

This is not the only way strings are handled. It's also common for platforms to represent strings with an initial integer indicating how many bytes the actual string character data uses. Since an integer takes more than one byte it means the string uses more space for storage, but certain other operations -- like finding the length --- can be faster, and it allows strings to contain characters like null-terminators as part of the string.

SQL Server itself uses something closer to the latter option, but many desktop applications, including SSMS, are based on programming environments that do the former.

When we convert(VARCHAR(MAX), 0x00004141), SQL Server is converting those 0 bytes at the front of the value into a null terminator character (two of them, actually). This is at the front of our new varchar string. I expect the AA characters are actually there in the string also, but they are after the null terminators. While SQL Server is perfectly happy to have a string like this, many other places that use this string will see it as empty.

One way around this is to limit yourself in the first step to a binary result only large enough for two characters. SELECT convert(varbinary(2), 16705) produces the 0x4141 value you want.

You can combine them like this:

convert(varchar(max), convert(varbinary(2), 16705))

But I also see this:

16-bit decimal value in SQL that represents one or two ASCII characters.

If we try this with a smaller value that should only have a single character, there is still oddness:

convert(varchar(max), convert(varbinary(2), 65))

This, again, produces an empty string, because the 65 becomes 0x0041 and there is still a leading null terminator. But this works:

convert(varchar(max), convert(varbinary(1), 65))

So it's likely you'll need some conditional check or CASE expression to know if you want a 1 or a 2 for the size of the varbinary conversion. Anything 255 or less should only get a 1.

I end up with an expression like this:

convert(varchar(max), case when @input <= 255 then convert(varbinary(1), @input) else convert(varbinary(2), @input) end)

Unfortunately, you can't put the case inside the varbinary length definition directly, to just select a 1 or 2 there, as this must be a constant/literal value.