Binary Conversion Styles: Hex With and Without 0x

Binary conversion styles decide whether hexadecimal text includes the 0x prefix and how SQL Server reads it back. I choose the style at both ends of an exchange. The same bytes can have two useful text representations without becoming ordinary text characters.

Separate bytes from their printed form

A binary payload can contain values that aren’t readable letters. Converting it to hexadecimal creates a text representation for inspection or exchange. That representation is different from interpreting the bytes as character data.

This example begins with four explicitly typed binary inputs. The longest contains bytes 41, 42, 00 and FF in hexadecimal. It also includes one zero byte, an empty value and NULL.

I return byte count beside each formatted string. That keeps the payload size visible when the printed text gets longer. The two hexadecimal digits representing a byte are characters in the output, not extra payload bytes.

Pair each style with its expected prefix

Style 1 is expected to show 0x414200FF for the four-byte input. Style 2 shows 414200FF without the prefix. Both outputs represent the same sequence of bytes.

The reverse conversion needs the matching input style. Style 1 expects the 0x prefix, while style 2 expects unprefixed hexadecimal digits. Neither style is an instruction to interpret the input as ordinary letters.

The two match columns compare each correctly paired round trip with the original bytes. Their expected result is 1 for every non-NULL input. The query keeps NULL results separate rather than calling missing data a successful payload.

The target lengths include enough space for every hexadecimal digit. The prefixed form needs two additional characters. Leaving the target length implicit would make an avoidable formatting assumption.

WITH Samples AS
(
    SELECT CaseId,Bytes
    FROM (VALUES
        (1,CAST(0x414200FF AS varbinary(4))),
        (2,CAST(0x00 AS varbinary(4))),
        (3,CAST(0x AS varbinary(4))),
        (4,CAST(NULL AS varbinary(4)))
    ) AS v(CaseId,Bytes)
)
SELECT CaseId,DATALENGTH(Bytes) AS ByteCount,
    CONVERT(varchar(10),Bytes,1) AS PrefixedHex,
    CONVERT(varchar(8),Bytes,2) AS PlainHex,
    CASE WHEN CONVERT(varbinary(4),CONVERT(varchar(10),Bytes,1),1)=Bytes
        THEN 1 WHEN Bytes IS NULL THEN NULL ELSE 0 END AS StyleOneMatches,
    CASE WHEN CONVERT(varbinary(4),CONVERT(varchar(8),Bytes,2),2)=Bytes
        THEN 1 WHEN Bytes IS NULL THEN NULL ELSE 0 END AS StyleTwoMatches
FROM Samples
ORDER BY CaseId;

Keep empty bytes and a zero byte distinct

A single zero byte is expected to display as 0x00 or 00. It still has a byte count of 1. Empty binary data has a byte count of zero.

The expected empty representations are 0x for style 1 and an empty string for style 2. That result is useful when reviewing export contracts. Neither should be replaced with a zero byte merely because the display looks empty.

NULL is expected to remain NULL in both conversions. That distinguishes absent data from a present empty payload. I preserve that difference before deciding what an external format permits.

Validate the whole exchange contract

Hexadecimal input requires complete pairs of valid digits for these styles. An invalid digit or incomplete pair is not a valid encoded payload. This demonstration uses valid inputs and does not intentionally raise conversion errors.

I’m tempted to remove 0x with an arbitrary string replacement. That hides which representation the sender promised. It is clearer to name the expected style and reject an unexpected format through the application’s validation policy.

A target binary or text length that is too small can shorten data. A successful conversion alone therefore cannot establish complete preservation. The round-trip comparisons here use lengths large enough for the supplied payloads.

I’d keep the original byte count with a serialized payload when truncation is a concern. Check the reconstructed bytes against the original contract as well. The sample match columns illustrate that check without writing a table or changing any settings.

These examples describe representation, not encryption or compression. Hexadecimal text does not hide the bytes or reduce their size. Its value is a clear and agreed text format for binary data.

Pick the style at both ends, and the bytes travel safely.

Hexadecimal is not encrypted data, it is a readable representation of the same bytes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.