UNISTR and Unicode Escapes in SQL Server 2025

A script can need a character that your keyboard does not offer. UNISTR, added in SQL Server 2025, builds that character from a Unicode escape while keeping the intended code point visible.

Make Character Intent Visible in the Script

Unicode assigns code points to characters. An escape sequence lets a script name those values without depending on its editor's font or keyboard layout. The function expands escapes inside a string expression. Ordinary literal text can remain beside the escaped characters, so the result is a readable complete value.

I use explicit escapes when a special character's identity matters in a test. A copied character can look similar to another code point or lose information during file handling. Naming the value makes review more precise. The script still needs correct storage and transmission after the character is created.

This feature belongs to SQL Server 2025. It does not make an earlier engine understand the function. Run the examples there and confirm the target application's character contract. The examples create values for inspection, rather than claiming that a particular display font rendered every symbol correctly.

SELECT UNISTR(N'caf\00E9') AS AccentedWord,
       UNISTR(N'\0928\092E\0938\094D\0924\0947') AS HindiGreeting,
       UNISTR(N'Hello \+01F600') AS EmojiGreeting;

Choose the UNISTR Escape Form for the Code Point

The four-digit form uses a backslash followed by four hexadecimal digits. The plus form uses a backslash, plus sign, and six hexadecimal digits. The latter expresses a full Unicode code point, including supplementary characters outside the basic multilingual plane. Keep the required digit width intact.

A supplementary character is represented by a surrogate pair in UTF-16 storage. You can express its two code units with four-digit escapes or its full value with the six-digit form. The complete code-point form makes the intent easier to recognize during review. A single surrogate code unit is incomplete character data.

SELECT UNISTR(N'\D83D\DE00') AS SurrogatePairForm,
       UNISTR(N'\+01F600') AS FullCodePointForm,
       CASE WHEN CONVERT(varbinary(max), UNISTR(N'\D83D\DE00')) =
                      CONVERT(varbinary(max), UNISTR(N'\+01F600'))
            THEN 1 ELSE 0 END AS SameStoredBytes;

The byte comparison checks representation rather than visual appearance. Similar glyphs can be different characters, and one character can require several code points in a written sequence. Do not infer byte equality from the fact that two values look alike in a results grid.

Keep the Unicode Literal Prefix

The N prefix marks a Unicode literal. Use it when building nvarchar values, including escaped sequences. That avoids passing the string through an unrelated legacy code page before the function evaluates it. Mixed ordinary text and escapes need the same care as any other Unicode expression.

For varchar or char input, use a supported UTF-8 collation. A legacy non-Unicode code page does not provide the function's required character support. The next query makes that UTF-8 input choice explicit. It also keeps the resulting value separate from an nvarchar comparison value.

SELECT UNISTR('caf\00E9' COLLATE Latin1_General_100_CI_AS_SC_UTF8)
       AS Utf8Expression,
       UNISTR(N'caf\00E9') AS UnicodeExpression;

I inspect parameter types as well as literal prefixes when adapting this to an application. A correct server expression can still receive damaged input from a narrow client parameter. Review the source file encoding, parameter definition, destination column, and readback. The earliest lossy conversion is where the repair belongs.

Select a Custom UNISTR Escape Character Deliberately

The optional second argument chooses a single escape character. This can simplify a literal that already contains backslashes. Pick one that does not conflict with ordinary accepted text. A custom escape is a parser choice, rather than an instruction to change Windows paths or other unrelated string conventions.

SELECT UNISTR(N'caf#00E9', N'#') AS CustomEscapeWord,
       UNISTR(N'Hello #+01F600', N'#') AS CustomEscapeEmoji,
       UNISTR(N'\0041') AS DefaultEscapeLetter;

Document the escape choice beside the expression when it is less familiar. Test values containing the custom character itself and reject malformed sequences through the input contract. Do not use blind character replacement to convert arbitrary external text into escape syntax. That mixes text interpretation with data normalization.

Compare NCHAR With UNISTR

NCHAR remains useful for producing one Unicode character from a numeric value. For several characters and surrounding text, the new function can express the sequence more directly. Both approaches still depend on correct Unicode handling. Their convenience differs, rather than one automatically providing a stronger data guarantee.

SELECT N'caf' + NCHAR(233) AS NcharWord,
       UNISTR(N'caf\00E9') AS EscapedWord;

Supplementary code-point handling through NCHAR depends on supplementary-character-aware collation behavior. Check that requirement before replacing a carefully tested existing expression. The escaped full code-point form provides an explicit alternative in this version. Keep accepted comparison cases when changing production string-generation logic.

Use CHAR for the contract it actually represents, rather than treating it as a substitute for all Unicode values. Numeric values in a legacy code page have different meanings from Unicode code points. A matching value for one familiar letter does not certify the mapping for another language.

Store the Created Characters Without Losing Them

Use nvarchar for a Unicode column, or varchar under an appropriate UTF-8 collation. Choose lengths for the accepted content and representation. A narrow legacy column can turn a successfully generated character into a question mark. The generating expression cannot prevent that later conversion.

CREATE TABLE #UnicodeSamples
(
    SampleID int NOT NULL PRIMARY KEY,
    UnicodeText nvarchar(100) NOT NULL,
    Utf8Text varchar(200) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL
);
INSERT #UnicodeSamples
VALUES (1, UNISTR(N'caf\00E9'), UNISTR(N'caf\00E9')),
       (2, UNISTR(N'\0928\092E\0938\094D\0924\0947'),
           UNISTR(N'\0928\092E\0938\094D\0924\0947')),
       (3, UNISTR(N'\+01F600'), UNISTR(N'\+01F600'));
SELECT SampleID, UnicodeText, Utf8Text,
       DATALENGTH(UnicodeText) AS UnicodeBytes,
       DATALENGTH(Utf8Text) AS Utf8Bytes
FROM #UnicodeSamples;

UNISTR names a character; its storage path must preserve the resulting value. Inspect the bytes and retrieved values on your own server. UTF-8 and UTF-16 use different storage lengths for different characters. A column length in bytes does not describe the same capacity as a Unicode code-unit limit. Include those boundaries in your storage tests.

Check Display and Comparison Separately

Which failure are you seeing, wrong stored bytes or a missing display glyph? A results grid can show a box because its font lacks the glyph. That visual problem differs from a lossy database conversion. Inspect the stored representation and test a supported application display before changing correct data.

Also define comparison rules for accents and composed character sequences. A collation can compare values as equivalent without their bytes being identical. Escaping one code point does not normalize every equivalent written form. Keep normalization and equality decisions in the application's documented text contract.

Use UNISTR to make special-character construction explicit and reviewable. Preserve the backslashes in the script, test malformed input, and verify the storage round trip. That gives the generated characters a reliable path from code-point intent to the text the reader receives.

Related reading on this blog: Datatype Storing Unicode Character Strings and UTF-8 Collations in SQL Server 2019: When They Save Space.

A Unicode escape is not a display guarantee, it is an explicit way to name character data.

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