Supplementary Characters

The data types nchar and nvarchar store each character as a 16-bit value in an encoding called UCS-2. This encoding, defined by versions of Unicode prior to 1996, supports characters in the range U+0000 to U+FFFF. Newer versions of Unicode have defined additional characters in the range U+10000 to U+10FFFF called supplementary characters. These characters are stored as pairs of 16-bit values, called surrogate pairs, in an encoding called UTF-16. All new _100 level collations support linguistic sorting with supplementary characters. 

If you use supplementary characters, consider the following limitations:

  • Supplementary characters can only be used in ordering and comparison operations in collation versions 90 or greater.

  • Because supplementary characters are stored as two 16-bit values, the LEN() function returns the value 2 for each supplementary character that is contained in the argument string. Similarly, the functions CHARINDEX and PATINDEX misrepresent the occurrence of supplementary characters inside character strings.

  • The LEFT, RIGHT, SUBSTRING, STUFF, and REVERSE functions may split any surrogate pairs and lead to unexpected results.

  • Supplementary characters are not supported for use with the underscore (_), percent (%), and caret (^) wildcard characters.

  • Supplementary characters are not supported for use in metadata, such as in names of database objects.

For a Transact-SQL script related to this scenario, see the Supplementary-Aware String Manipulation sample. For information about samples, see Considerations for Installing SQL Server Samples and Sample Databases.

Community Additions