如何將 SQL Server Unicode / NVARCHAR 字元串設置為表情符號或補充字元？

我想根據其 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 中的 char、nchar、varchar 和 nvarchar 有什麼區別？
• Microsoft 的補充字元整理資訊
• Microsoft 的 SQL Server 2008 R2 排序規則列表

有沒有解釋為什麼，不管排序規則，SQL Server 可以從 ? 的角度理解和處理擴展字元NCHAR？

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 根本無法將它們映射到任何字元，因此它們在內置函式中顯示為兩個未知字元。NCHAR``XML``NTEXT

---

考慮到這些背景資訊，我們現在可以解決具體問題：

我想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 字元（是的，包括表情符號😸）

引用自：https://dba.stackexchange.com/questions/139551