UNICODE: Supplementary Characters Depend on the Collation

UNICODE returns an integer for the first character, and supplementary-character support affects that integer. I make the collation explicit when interpreting those values. Otherwise the same visible input can produce a misleading comparison.

Many familiar letters fit inside Unicode’s basic multilingual plane. Some symbols require a UTF-16 surrogate pair instead. A supplementary-aware collation lets UNICODE interpret that pair as the complete character.

Compare two explicit collations

The query uses Unicode input literals and an nvarchar column. Its rows contain an ASCII letter, an accented letter, a supplementary symbol, a two-letter string and NULL. Every row is preserved in the result.

The first calculation uses Latin1_General_100_CI_AS_SC. The second uses the corresponding collation without supplementary-character support. Neither calculation depends on changing the current database collation.

The numeric outputs are the useful comparison here. A font may display the supplementary symbol differently or lack its glyph. That visual limitation does not change the integer returned by the expression.

WITH Inputs AS
(
    SELECT CaseId, InputText
    FROM (VALUES (1, CAST(N'A' AS nvarchar(20))), (2, N'é'),
                 (3, N'😀'), (4, N'AB'), (5, NULL)) AS v(CaseId, InputText)
)
SELECT CaseId, InputText,
       UNICODE(InputText COLLATE Latin1_General_100_CI_AS_SC) AS SupplementaryAwareCodePoint,
       UNICODE(InputText COLLATE Latin1_General_100_CI_AS) AS NonSupplementaryCodeUnit
FROM Inputs
ORDER BY CaseId;

Read the expected code points

The letter A should return 65 under both collations. The accented letter é should return 233 under both. These inputs fit within the basic multilingual plane, so the two calculations agree.

The supplementary symbol is U+1F600. Under the supplementary-aware collation, its expected integer is 128512. The other expression returns 55357, the first UTF-16 surrogate code unit rather than the complete symbol’s code point.

The AB row returns 65 twice because UNICODE inspects the first character only. It does not summarize the string or return one number per character. A longer input therefore needs additional logic if every position matters.

The missing input remains NULL. I keep that case separate from ordinary character values. Replacing missing input with a default letter would create a code point that was never present in the source.

Keep encoding and collation questions separate

The two expressions operate on the same nvarchar input. They illustrate interpretation, not a conversion from a non-Unicode code page. Using an explicit collation cannot recover a character lost before this query received it.

The N prefix matters when the SQL source contains Unicode literals. Keep the source file and client transmission Unicode-capable too. A question mark already substituted by an earlier conversion is a different input from the original symbol.

Supplementary-aware support for this use was introduced in SQL Server 2012. The example names the chosen collations instead of relying on a default. That makes its intended interpretation visible during review.

A code point also does not necessarily describe an entire user-perceived grapheme. A displayed character can involve multiple Unicode code points. UNICODE’s first-character integer is therefore not a general text segmentation algorithm.

Use the integer for a specific purpose

I use a code-point diagnostic to explain what a string expression is reading. It can reveal an unexpected first character or an interpretation difference. It does not establish that a whole input is valid for an application.

For complete text validation, define the permitted characters and how combined sequences should be handled. Do not infer those rules from one returned integer. Case, accents and other comparison choices still need their own requirements.

The query contains no schema changes or session-setting commands. It compares all five inputs using explicit expressions. Its expected results describe the chosen collations, not every collation installed on every SQL Server.

When reviewing the output, retain the original text beside both numbers. That prevents a partial-code-unit result from being mistaken for the symbol’s full identity. It also makes the first-character limit clear for longer strings.

Pick the collation on purpose, and the numbers stop being a surprise.

A code point is not a code unit, it is the whole character a collation must read.

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