主页 / dba / 问题 / 139551
Accepted
Riley Major
Asked: 2016-05-26 12:02:32 +0800 CST

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

  • 772

我想根据其 Unicode 代码点将 Unicode 字符串变量设置为特定字符。

我想使用超过 65535 的代码点，但 SQL Server 2008 R2 数据库的排序规则为SQL_Latin1_General_CP1_CI_AS.

根据微软的 NCHAR 文档，该NCHAR函数采用如下整数：

整数表达式

当数据库的排序规则不包含补充字符 (SC) 标志时，这是一个从 0 到 65535（0 到 0xFFFF）的正整数。如果指定了超出此范围的值，则返回 NULL。有关补充字符的更多信息，请参阅排序规则和 Unicode 支持。

当数据库的排序规则支持补充字符 (SC) 标志时，这是一个从 0 到 1114111（0 到 0x10FFFF）的正整数。如果指定了超出此范围的值，则返回 NULL。

所以这段代码：

SELECT NCHAR(128512);

NULL在此数据库中返回。

我希望它返回与此相同的内容：

SELECT N'?';

如何在排序规则“不包含补充字符 (SC) 标志”的数据库中使用代码（不使用实际表情符号字符）将 Unicode 字符串变量（例如 nvarchar）设置为表情符号？

表情符号 Unicode 代码点的完整列表

（最终我希望任何角色都能工作。我只是选择了表情符号以方便参考。）

（虽然服务器是SQL Server 2008 R2，但我也很好奇以后版本有什么解决方案。）

假设没有办法，我可以在另一个具有适当排序规则的数据库中引用内联用户定义函数吗？

如何找到具有“补充字符”标志的排序规则？

这在我们的服务器上不返回任何记录：

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

似乎引入Latin1_General_100_CI_AS_SC的 SQL Server 2012 会起作用。您可以在较旧的实例上安装排序规则吗？

整理参考：

有没有解释为什么，不管排序规则，SQL Server 可以从 ? 的角度理解和处理扩展字符NCHAR？

sql-server t-sql

1 个回答

  1. Best Answer

    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是UCS-2)。并且，UTF-16 的 65536 - 1114111 (0x10000 - 0x10FFFF) 范围是由 UCS-2 范围（特别是范围 0xD800 – 0xDBFF 和 0xDC00 – 0xDFFF）中的两个代码点构成的，它们为此目的而保留，否则没有意义。两个代码点的这种组合称为代理对，代理对表示超出 UCS-2 范围的字符，称为补充字符。

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

    1. NCHAR()当不使用 Supplementary Character-Aware Collat​​ion (SCA; ie with _SC, or _140_ but not in the name) 时，一些内置函数（不仅仅是）不处理代理对/补充字符，_BIN*因为非 SCA 排序规则（尤其是SQL_排序规则）最初是在 UTF-16 完成之前实现的（我相信是在 2000 年的某个时候）。SQL_在比较和排序方面具有_90_或_100_在其名称中但没有_SC对补充字符的最小支持的非排序规则。
    2. 完整的 Unicode / UTF-16 字符集可以存储在 // 数据类型中，而不会丢失任何数据，NVARCHAR因为UCS -2 和 UTF-16 是完全相同的字节序列。唯一的区别是 UTF-16 使用代理代码点来构造代理对，而 UCS-2 根本无法将它们映射到任何字符，因此它们在内置函数中显示为两个未知字符。NCHARXMLNTEXT

    考虑到这些背景信息，我们现在可以解决具体问题：

    我想SELECT NCHAR(128512);返回与此相同的内容：SELECT N'?';

    仅当当前数据库（正在执行查询的位置）具有可识别补充字符的默认排序规则时才会发生这种情况，并且这些排序规则是在 SQL Server 2012 中引入的。具有字符串输入参数的内置函数可以提供排序规则通过COLLATE子句 (ie LEN(N'string' COLLATE Some_Collation_SC)) 内联，并且不需要在具有 SCA 默认排序规则的数据库中执行。NCHAR()但是，诸如接受INT输入参数和子句之类的内置函数COLLATE在该上下文中无效（这就是为什么NCHAR()仅在当前数据库具有可识别补充字符的默认排序规则时才支持补充字符的原因；但这是不必要的可以更改的不便之处，所以请投票支持我的建议：无论活动数据库的默认排序规则如何，NCHAR() 函数都应始终返回值 0x10000 - 0x10FFFF 的补充字符。

    有没有解释为什么，不管排序规则，SQL Server 可以从 ? 的角度理解和处理扩展字符NCHAR？

    此答案的顶部解释了 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 的补充字符排序信息”）并向下滚动一点，您将看到内置函数的图表以及它们如何基于有效排序规则运行。

    如何找到具有“补充字符”标志的排序规则？

    在 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标志；是的，查询上面显示的内容以及_UTF8SQL Server 2019 中添加的排序规则）。

    您可以在较旧的实例上安装排序规则吗？

    不可以，不能将排序规则安装到以前版本的 SQL Server 中。

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

    不使用 SCA Collat​​ion 时，可以通过两种方式注入 65535 / U+FFFF 以上的 Code Points：

    1. 根据对NCHAR()函数的两次调用指定代理对，每次调用都包含该对的一部分
    2. VARBINARY根据转换小端（即反转）字节序列的形式来指定代理对。

    即使有效的排序规则是补充字符感知的，这两种插入补充字符/代理对的方法也可以工作，并且应该在所有版本的 SQL Server 中都可以正常工作，至少早在 2005 年（尽管可能也可以在SQL Server 2000 也是如此）。

    例子：

    • 性格：
                         ? ==
    • 名称：                一堆便便
    • 十进制：            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)

    更新

    您可以使用以下 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（不需要“ _SC”或“ _140_”排序规则）、HTML（和 XML）中的点（BMP 和补充字符），许多应用程序语言共有的样式（“\uHHHH”；用于 C++ / C# / F# /Java / JavaScript / Julia / 等），最后是稍微更新的其他常见样式，它处理所有代码点，而不仅仅是 BMP（“\UHHHHHHHHH”；用于 C / C++ / C# / F# / Julia / 等）。

    在这里阅读所有相关信息：

    SSMS 提示 #3：轻松访问/研究所有 Unicode 字符（是的，包括表情符号?）

    • 49

相关问题