SUBSTRING Binary: Count Bytes Rather Than Characters

SUBSTRING binary positions count bytes, while a character expression uses its character-counting rules. I identify the input type before choosing offsets. The same numeric arguments can select very different pieces.

Slice text and its encoded bytes side by side

The first input is a short Unicode string containing ABCD. The query converts that value to varbinary without replacing its content. The expected hexadecimal representation is 4100420043004400.

The character slice starts at position two and requests two characters. Its expected text is BC. Those two basic Unicode characters occupy four bytes in this nvarchar example.

The binary slice also starts at position two and requests two units. Its expected hexadecimal text is 0042. Those units are bytes, so the slice crosses a Unicode character boundary.

I display binary results as hexadecimal text rather than decoding every arbitrary slice as text. A byte segment need not be a complete encoded character sequence. Hexadecimal preserves what the query selected.

WITH Inputs AS
(
    SELECT CaseId,TextValue
    FROM (VALUES (1,CAST(N'ABCD' AS nvarchar(8))),
        (2,CAST(N'' AS nvarchar(8))),(3,CAST(NULL AS nvarchar(8)))) AS v(CaseId,TextValue)
), Encoded AS
(
    SELECT *,CONVERT(varbinary(16),TextValue) AS BinaryValue FROM Inputs
)
SELECT CaseId,TextValue,SUBSTRING(TextValue,2,2) AS CharacterSlice,
    CONVERT(varchar(32),BinaryValue,2) AS WholeHex,
    CONVERT(varchar(32),SUBSTRING(BinaryValue,2,2),2) AS ByteSliceHex,
    CONVERT(varchar(32),SUBSTRING(BinaryValue,3,4),2) AS AlignedSliceHex,
    DATALENGTH(SUBSTRING(TextValue,2,2)) AS CharacterSliceBytes,
    DATALENGTH(SUBSTRING(BinaryValue,2,2)) AS ByteSliceBytes
FROM Encoded ORDER BY CaseId;

Align a byte segment only when the encoding is known

The AlignedSliceHex column starts at byte three and requests four bytes. Its expected result is 42004300. For this specific basic-plane string, those bytes correspond to the character slice BC.

That alignment follows this input’s encoding and characters. It is not a rule that every character consumes two bytes under every type. A varchar value can use a different encoding entirely.

Unicode supplementary characters also deserve separate consideration. Character counting under supplementary-character collations treats a valid surrogate pair as one character. Binary slicing still works with bytes rather than that character unit.

I keep the input deliberately small and ordinary. The example establishes the unit difference without pretending to solve arbitrary text segmentation. A general decoder must follow the complete encoding contract.

Keep empty and missing inputs visible

The second row supplies an empty Unicode string. Its expected character slice and binary slices are empty. Both byte-count outputs are zero.

The third row supplies a typed SQL NULL. Its expected slices and byte counts remain NULL. A missing input is therefore distinguishable from a present input with no content.

I retain those cases beside the populated row. An all-filled example cannot show whether later application code merges empty and missing results. That distinction often matters when slicing identifiers or payloads.

The final display order uses the unique CaseId. It makes the expected tuple order explicit. It does not change the meaning of any substring offset.

Choose the unit required by the receiving operation

A text field requirement usually describes characters or a linguistic rule. A binary protocol requirement usually describes byte offsets. Choose the source expression accordingly before applying SUBSTRING.

Casting a sliced binary value into text afterward does not retroactively change the selected byte boundaries. The cut has already happened. A later display conversion cannot recover bytes outside the slice.

This query supplies an explicit length argument for compatibility with SQL Server 2022 and earlier syntax. Newer optional-length syntax is unnecessary here. All offsets and lengths are positive constants.

Read the character slice and hexadecimal segment as different units. The character result describes text positions. The binary result describes byte positions inside that text’s encoded representation.

Know the unit before you pick the offsets, and the slice lands where you meant.

A byte offset is not a character position, it is a position in the encoded bytes.

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