如何将SQL Server Unicode / NVARCHAR字符串设置为表情符号或补充字符？

 SELECT NCHAR(128512);
 

 SELECT N'😀';
 

 SELECT * FROM sys.fn_helpcollations() 
WHERE name LIKE 'SQL%[_]SC';
 

#1 楼

UCS-2编码始终为每个字符2个字节，范围为0-65535（0x0000-0xFFFF）。 UTF-16（无论大端优先还是小端优先）的范围为0-1114111（0x0000-0x10FFFF）。 UTF-16的0-65535 / 0x0000-0xFFFF范围是每个字符2个字节，而超过65536 / 0xFFFF的范围是每个字符4个字节。

Windows和SQL Server使用UCS-2开始编码，因为它可用并且UTF-16尚未完成。但是，幸运的是，在UCS-2和UTF-16的设计中已经有了足够的前瞻性，以至于UCS-2映射是UTF-16映射的完整子集（意味着：0-65535 / 0x0000-0xFFFF范围UTF-16的USC是UCS-2）。 AND，UTF-16的65536-1114111（0x10000-0x10FFFF）范围是由UCS-2范围内的两个代码点（特别是范围0xD800 – 0xDBFF和0xDC00 – 0xDFFF）构成的，否则没有含义。这两个代码点的组合称为代理对，代理对表示UCS-2范围以外的字符，称为补充字符。

所有这些信息解释了NVARCHAR / Unicode的两个方面SQL Server中的数据：


多个内置函数（不只是NCHAR()）在不使用识别辅助字符的排序规则（SCA；即一个）时不处理代理对/补充字符使用_SC或_140_而非名称中的_BIN*，因为非SCA归类（尤其是SQL_归类）最初是在UTF-16完成之前实现的（我相信是在2000年）。名称中带有SQL_或_90_但不带有_100_的非_SC归类在比较和排序方面对补充字符的支持最少。
完整的Unicode / UTF-16字符集可以存储在NVARCHAR / NCHAR / XML / NTEXT数据类型中，而不会造成任何数据丢失，因为UCS-2和UTF-16是完全相同的字节序列。唯一的区别是UTF-16利用代理代码点构造代理对，而UCS-2根本无法将它们映射到任何字符，因此它们在内置函数中显示为两个未知字符。 />

有了这些背景信息，我们现在可以解决以下特定问题：


我希望SELECT NCHAR(128512);返回与此相同的内容：SELECT N'😀';


只有在当前数据库（正在执行查询的数据库）具有默认字符排序规则（补充字符识别）并且在SQL Server 2012中引入了默认排序规则时，才会发生这种情况。在具有字符串输入参数的函数中，可以通过COLLATE子句（即LEN(N'string' COLLATE Some_Collation_SC)）内联提供排序规则，而无需在具有SCA默认排序规则的数据库中执行。但是，诸如NCHAR()之类的内置函数接受一个INT输入参数，而COLLATE子句在那种情况下无效（这就是为什么当当前数据库具有默认排序规则为Supplementary Aware-Aware时，NCHAR()仅支持Supplementary Characters的原因；但是这是可以避免的不必要麻烦，因此请投票赞成我的建议：NCHAR（）函数应始终返回值0x10000-0x10FFFF的补充字符，而不考虑活动数据库的默认归类）。


是否有解释为什么从NCHAR的角度来看，SQL Server不管排序规则如何都可以理解和处理扩展字符？


此答案的顶部解释了SQL Server如何存储和检索补充字符而不会丢失数据。但是，并不是真的NCHAR是唯一的补充字符有问题的内置函数（当不使用SCA排序规则时）。例如，LEN(N'😀' COLLATE SQL_Latin1_General_CP1_CI_AS)返回值2，而LEN(N'😀' COLLATE Latin1_General_100_CI_AS_SC)返回值1。

如果转到问题中发布的第二个链接（即“ Microsoft的补充字符归类信息”）并向下滚动，稍微看一下，您将看到一个内置函数的图表，以及它们基于有效归类的行为。


如何找到具有“ supplementary character”标志的归类？


在2012年之前的SQL Server版本中不能。但是，从SQL Server 2012开始，您可以使用以下查询：

 SELECT col.*
FROM   sys.fn_helpcollations() col
WHERE  col.[name] LIKE N'%[_]SC'
OR     col.[name] LIKE N'%[_]SC[_]%'
OR     (COLLATIONPROPERTY(col.[name], 'Version') = 3
      AND col.[name] NOT LIKE N'%[_]BIN%');
 

您的查询已关闭，但是模式以SQL开头，而SQL Server排序规则（即以SQL_开头的排序规则）已被弃用了一段时间，以支持Windows排序规则（不是以SQL_开头的排序规则）。因此，不会更新SQL_归类，因此没有包含_SC选项的较新版本（从SQL Server 2017开始，所有新归类均自动支持补充字符，并且不需要或没有_SC标志;是，则紧接在上方的查询将解决该问题，并获取SQL Server 2019中添加的_UTF8归类。）


可以在较旧的实例上安装归类吗？


否，您不能将排序规则安装到SQL Server的早期版本中。


在排序规则“不包含补充字符（SC）标志”的数据库中，如何使用代码（不使用实际的补充字符）将Unicode字符串变量（例如nvarchar）设置为补充字符？
。 ..
尽管服务器是SQL Server 2008 R2，但我也对更高版本的解决方案感到好奇。


不使用SCA排序规则时，可以插入代码超过65535 / U + FFFF的点有两种方式：

通过两次调用NCHAR()函数指定代理对，每个调用都具有一对对的一部分
指定就转换Little Endian（即反向）字节序列的VARBINARY形式而言，代理对。

即使有效的归类是可识别补充字符的，这两种插入辅助字符/代理对的方法也将起作用。 ，并且在所有版本的SQL Server中都应相同，至少可以追溯到2005年（尽管可能会也可以在SQL Server 2000中使用）。

示例：



字符：

💩


名称：Poo Poo堆

十进制：128169

代码点：U + 1F4A9

代理对： U + D83D和U + DF21

 SELECT N'💩', -- 💩
       UNICODE(N'💩' COLLATE Latin1_General_100_CI_AS), -- 55357
       UNICODE(N'💩' COLLATE Latin1_General_100_CI_AS_SC), -- 128169
       NCHAR(128169), -- 💩 in DB with _SC Collation, else NULL
       NCHAR(0x1F4A9), -- 💩 in DB with _SC Collation, else NULL
       CONVERT(VARBINARY(4), 128169), -- 0x0001F4A9
       CONVERT(VARBINARY(4), N'💩'), -- 0x3DD8A9DC
       CONVERT(NVARCHAR(10), 0x3DD8A9DC), -- 💩 (regardless of DB Collation)
       NCHAR(0xD83D) + NCHAR(0xDCA9) -- 💩 (regardless of DB Collation)
 

UPDATE

您可以使用以下iTVF从65536-1114111（0x010000-0x10FFFF）之间的任何代码点获取代理对值（形式为INT和BINARY）。并且，尽管输入参数的类型为INT，但您可以传递代码点的二进制/十六进制形式，它将隐式转换为正确的整数值。

 CREATE FUNCTION dbo.GetSupplementaryCharacterInfo(@CodePoint INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH calc AS
(
  SELECT 55232 + (@CodePoint / 1024) AS [HighSurrogateINT],
         56320 + (@CodePoint % 1024) AS [LowSurrogateINT]
  WHERE  @CodePoint BETWEEN  65536 AND 1114111
)
SELECT @CodePoint AS [CodePointINT],
       HighSurrogateINT,
       LowSurrogateINT,
       CONVERT(VARBINARY(3), @CodePoint) AS [CodePointBIN],
       CONVERT(BINARY(2), HighSurrogateINT) AS [HighSurrogateBIN],
       CONVERT(BINARY(2), LowSurrogateINT) AS [LowSurrogateBIN],
       CONVERT(binary(4), NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT)) AS [UTF-16LE],
       NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT) AS [Character]
FROM   calc;
GO
 

使用上述功能，可以进行以下两个查询：

 SELECT * FROM dbo.GetSupplementaryCharacterInfo(128169);

SELECT * FROM dbo.GetSupplementaryCharacterInfo(0x01F4A9);
 

都返回以下内容：

 CodePoint  HighSurrogate  LowSurrgate  CodePoint  HighSurrgate  LowSurrgate  UTF-16LE   Char
INT        INT            INT          BIN        BIN           BIN                     actr
128169     55357          56489        0x01F4A9   0xD83D        0xDCA9       0x3DD8A9DC   💩
 

更新2：更好的更新！

我已经修改了上面显示的iTVF，现在可以返回188,657个代码点，因此您无需将其设置为任何特定值。当然，作为TVF，您可以添加WHERE子句以对特定代码点，代码点范围或“相似字符”等进行过滤。并且，它包括带有预格式化转义序列的其他列，以构造每个T-SQL中的代码点（BMP和补充字符）（不需要“ _SC”或“ _140_”排序规则），HTML（和XML），这是许多应用程序语言（“ \ uHHHH”）通用的样式；用于C ++ / C＃/ F＃/ Java / JavaScript / Julia /等），最后是稍稍较新的其他通用样式，可处理所有代码点，而不仅仅是BMP（“ \ UHHHHHHHH”；用于C / C ++ / C＃/ F＃/ Julia等） ）。

在此处了解所有内容：

SSMS技巧3：轻松访问/研究所有Unicode字符（是，包括表情符号😸）