Lenni's Technology Blog

This post explains the new UNISTR function in SQL Server 2025, a small but handy way to embed Unicode codepoints directly into string literals using escape sequences.

Before SQL Server 2025, the only way to insert Unicode characters by their numeric codepoints was to use the NCHAR() function — one character at a time — and concatenate them manually. This approach worked but was verbose, hard to read, and error-prone when dealing with complex strings or emoji sequences.

While the NCHAR function is SQL Server–specific and not ANSI-compliant, UNISTR is part of the ANSI SQL standard and is already supported by other databases (like Oracle). Its addition to T-SQL improves cross-platform consistency and standards compliance, which is an important consideration when writing portable SQL code.

With UNISTR, you now have a cleaner, more readable, and standards-compliant way to embed multiple Unicode characters using escape sequences, all in a single function call. This is essentially a convenient shorthand for performing multiple NCHAR() calls and string concatenations in a single expression.

Furthermore, UNISTR supports Unicode escape sequences like \xxxx (for UTF-16 code units) and \+xxxxxx (for full Unicode codepoints). This makes it much easier to work with emojis, symbols, accented characters, and multilingual strings directly in T-SQL.

Before and After: UNISTR vs NCHAR

Here’s an example showing how the old NCHAR function requires clumsy string concatentation to compose a Unicode string with emojis:

SELECT CONCAT('I ', NCHAR(0x2665), ' SQL Server 2025 ', NCHAR(0xD83D), NCHAR(0xDE03), '.')

This query produces the following output:

I ♥ SQL Server 2025 😃.

But with UNISTR, you can achieve the same result much more cleanly using a single string with no concatenation:

SELECT UNISTR(N'I \2665 SQL Server 2025 \D83D\DE03.')

The first version requires string concatenation plus three calls to NCHAR to compose the result. The second version uses UNISTR and is much cleaner and self-contained. Both versions use UTF-16 surrogate pairs to represent the second emoji (laughing face), but UNISTR works with a single function call.

UTF-16 Surrogate Pairs vs Full Unicode Codepoints

This alternate version also works, but uses the full Unicode codepoint escape format to represent the second emoji, rather than UTF-16 surrogate pairs:

SELECT UNISTR(N'I \2665 SQL Server 2025 \+01F603.')

Full Unicode codepoints are preferred over UTF-16 surrogate pairs, because they are generally more readable and easier to maintain, especially when dealing with longer strings or multiple Unicode characters. It’s also more convenient for looking up special characters in various Unicode reference tables (such as https://unicode.org/emoji/charts/full-emoji-list.html).

Custom Escape Character

UNISTR also lets you define a custom escape character (instead of the default backslash \). This is helpful if you need to treat the backslash as a literal character.

For example, the following query uses $ as the escape character instead of \, which is now treated as an ordinary literal:

SELECT UNISTR(N'I $2665 SQL Server 2025 \ Azure SQL Database $+01F603.', '$')

In this query, the backslash is treated as a regular character, while $ is used to escape the Unicode content:

I ♥ SQL Server 2025 \ Azure SQL Database 😃.

Summary

• NCHAR() requires one call per character and manual concatenation.
• UNISTR() simplifies this by allowing multiple Unicode escapes in a single string with a single function call.
• Supports \xxxx (UTF-16) and \+xxxxxx (full Unicode codepoints) escape formats.
• You can define custom escape characters as needed.

UNISTR makes your Unicode handling in SQL Server 2025 easier, more readable, and more maintainable — especially when dealing with emojis, multilingual text, or symbols.