Sql-Server
我應該對聖經希伯來語使用哪種排序規則？
我應該為聖經希伯來語使用哪種 SQL Server 排序規則？正在考慮的數據庫需要適應變音符號（即元音、重音、比喻等）。
**第一：**聖經希伯來語和現代希伯來語之間沒有區別，在整理方面。我們只是在處理希伯來語。
第二： ~~無論如何，您都想使用最新的排序規則集，
_100_因為它們比名稱中沒有版本號的舊系列（技術上是版本80）具有更新/更完整的排序權重和語言規則。~~通常，我強烈建議使用您需要的任何排序規則的最新版本，但至少在這種情況下，有充分的理由使用名稱中沒有版本號的版本。版本 100（或更新的）排序規則要完整得多，並且可以區分補充字元（如果使用SCor140排序規則，甚至完全支持它們），但假設您不處理補充字元，那麼版本 80（沒有版本名稱中的數字）排序規則在處理希伯來語方面做得更好（參見下面的“第六”項）。**第三：**希伯來語中沒有“假名”（或假名類型）的概念，因此您可以忽略
_KS其名稱中的任何排序規則變化（因為這是您永遠不會使用的敏感性）。**第四：**以支持補充字元（即完整的 UTF-16）結尾的排序規則，
_SC因此通常最好選擇其中一個，如果可用（意思是：如果您使用的是 SQL Server 2012 或更高版本）。**第五：**您不想要二進制排序規則（
_BIN或_BIN2），因為它們無法區分具有相同但具有不同順序的組合字元的元音和音標的希伯來字母，也不能忽略元音和其他標記等同於א和之類的東西אֽ。例如（元音和音節符號以相反的順序組合字元）：
SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8), NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) = NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_CS_AS_SC; -- אָ֜ אָ֜ SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8), NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) = NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_BIN2; -- no rows**第六：**這取決於您將如何與字元串值互動。希伯來語沒有大寫/小寫字母，但有一些程式碼點會受到區分大小寫的影響。甚至還有一些對寬度敏感的程式碼點。重音敏感/不敏感會影響用於元音、發音和音符（即比喻）的變音符號。
- 您是否需要區分同一字母的最終形式和非最終形式？希伯來語中有五個字母在用作單詞的最後一個字母時看起來不同。SQL Server 通過區分大小寫/
_CS排序規則來處理這個問題（但不幸的是，它似乎在更新的，通常更好的版本 100 和更新的排序規則中被破壞了）：SELECT NCHAR(0x05DE) AS [Mem], NCHAR(0x05DD) AS [Final Mem] WHERE NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CI_AS_KS_WS; -- 1 row (expected; all sensitive except case) -- Mem Final Mem -- מ ם SELECT NCHAR(0x05DE) AS [Mem], NCHAR(0x05DD) AS [Final Mem] WHERE NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CS_AI; -- no rows (expected; all insensitive except case) SELECT NCHAR(0x05DE) AS [Mem], NCHAR(0x05DD) AS [Final Mem] WHERE NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_100_CI_AI; -- no rows (expected 1 row; all insensitive)
- 您需要區分發音標記、元音和音標嗎？SQL Server 通過重音敏感/
_AS排序規則來處理這個問題（但不幸的是，它似乎在更新的，通常更好的版本 100 和更新的排序規則中被破壞了）。請注意，所有這三個都在重音敏感下分組在一起，並且不能單獨控制（即您不能對元音敏感但對中音符號不敏感）。發音標記
有幾個字母有兩種不同的發音。有時，使用哪種聲音的唯一指標是字母所在單詞的上下文（有時甚至是周圍的單詞），例如在實際的妥拉中（沒有發音標記或元音）。但是，其他形式的相同文本以及其他文本將在字母內部或字母上方的字母上方放置點。字母 Shin 可以有“sh”或“s”的聲音。為了表示“sh”音（即字母“shin”），右側上方有一個點，而左側上方有一個點表示“s”音（字母“sin”）：
SELECT NCHAR(0x05E9) AS [Shin], -- ש NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot] -- שׂ WHERE NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CS_AI_KS_WS AND NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CS_AI_KS_WS; -- 1 row (expected; all sensitive except accent) SELECT NCHAR(0x05E9) AS [Shin], -- ש NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot] -- שׂ WHERE NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CI_AS OR NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CI_AS; -- no rows (expected; all insensitive except accent) SELECT NCHAR(0x05E9) AS [Shin], -- ש NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot] -- שׂ WHERE NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_100_CI_AI_SC OR NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_100_CI_AI_SC; -- no rows (expected 1 row; all insensitive)元音
SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CS_AI_KS_WS; -- 1 row (expected; all sensitive except accent) SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CI_AS; -- no rows (expected; all insensitive except accent) SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_100_CI_AI_SC; -- no rows (expected 1 row; all insensitive)懸垂標記
從技術上講，根據官方 Unicode 數據，希伯來語中音符號是可以忽略的，並且僅在使用二進制排序規則時才*應在此處註冊為差異。*但是，SQL Server 將它們視為重音（不幸的是）並且不能將它們與發音標記或元音分開忽略。
SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CS_AI_KS_WS; -- 1 row (expected; all sensitive except accent) SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CI_AS; -- no rows (expected; all insensitive except accent) SELECT NCHAR(0x05D0) AS [Aleph], -- א NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨ WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_100_CI_AI_SC; -- no rows (expected 1 row; all insensitive)
- 您需要區分同一個字母的寬格式和非寬格式嗎？希伯來語中有八個字母被拉伸（寬）但僅用於在托拉捲軸中使用（手寫/真實或印刷）以保持完全合理的柱狀格式（實際上是它在托拉捲軸中的顯示方式）。SQL Server 通過寬度敏感度/
_WS排序規則來處理這個問題（有趣的是，它似乎是唯一在較新版本 100 和較新排序規則中正常工作的敏感度，但遺憾的是，它是最不可能使用的）：SELECT NCHAR(0x05DC) AS [Lamed], NCHAR(0xFB25) AS [Wide Lamed] WHERE NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_CI_AI; -- no rows (expected 1 row; all insensitive) SELECT NCHAR(0x05DC) AS [Lamed], NCHAR(0xFB25) AS [Wide Lamed] WHERE NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CS_AS_KS_SC; -- 1 row (expected; all sensitive except width) -- Lamed Wide Lamed -- ל ﬥ SELECT NCHAR(0x05DC) AS [Lamed], NCHAR(0xFB25) AS [Wide Lamed] WHERE NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CI_AI_WS_SC; -- no rows (expected; all insensitive except width)因此，也許
Hebrew_CI_AI對於列，COLLATE如果您需要使用變體，例如COLLATE Hebrew_CS_AIorHebrew_CI_AS或，您可以通過語句覆蓋每個表達式/謂詞Hebrew_CS_AS。補充筆記
- 您需要將數據儲存在
NVARCHAR列/變數中。您可以使用Windows-1255 程式碼頁（這是所有排序規則使用的）在正常 8 位中完成大部分操作，包括組合元音和發音點的字元：VARCHAR``Hebrew_*;WITH Hebrew AS ( SELECT NCHAR(0x05E9) + NCHAR(0x05C1) + NCHAR(0x05B8) COLLATE Hebrew_100_CS_AS AS [Shin] ) SELECT Hebrew.[Shin] AS [Unicode], CONVERT(VARCHAR(20), Hebrew.[Shin]) AS [CodePage1255], CONVERT(VARBINARY(10), CONVERT(VARCHAR(20), Hebrew.[Shin])) AS [CodePage1255_bytes] FROM Hebrew; -- Unicode CodePage1255 CodePage1255_bytes -- שָׁ שָׁ F9D1C8但是，只有 Unicode希伯來語塊包含中音符號（即比喻；程式碼點 U+0591 到 U+05AF）加上一些額外的（程式碼點 U+05C4 到 U+05C7），而Alphabetic Presentation Forms 塊包含寬幾個字母的變體加上其他一些東西。 2. 請參閱官方Unicode 標準（版本 12.1），第 9 章：中東-I，第 9.1 節：希伯來語，第 361-366 頁（PDF 的 389-394）以獲取有關 Unicode 如何處理希伯來語文本的更詳細說明。 3. 根據希伯來語（“he”和“he-IL”）文化的官方 Unicode CLDR（特定於語言環境的剪裁）規則，U+05F3 HEBREW PUNCTUATION GERESH 應該匹配或位於 U+0027 APOSTROPHE之前。通常，U+05F3 排在撇號之後。當使用ICU 排序展示並在“根”/標準排序順序（由美國英語/“en-US”使用）和“he”之間切換時，確實可以看到這種行為。但是，這種行為在 .NET 或 SQL Server 中似乎都不可用：
SELECT NCHAR(0x05F3) WHERE NCHAR(0x05F3) <= N'''' COLLATE Hebrew_100_CS_AS_KS_WS; -- no rows SELECT NCHAR(0x05F3) WHERE NCHAR(0x05F3) <= N'''' COLLATE Hebrew_CS_AS_KS_WS; -- no rows雖然是一個不幸的遺漏，但考慮到我在 Windows 排序權重表文件中沒有看到任何“he”或“he-IL”特定的剪裁，這確實是有道理的。這很可能意味著關聯程式碼頁之外的排序規則
Hebrew_*和Latin1_General_*排序規則之間沒有實際區別，關聯程式碼頁僅用於VARCHAR數據，此處不適用。 4. 請參閱相關問答：如何去除希伯來語重音標記OP 回复：
是的，我需要區分：1）同一字母的最終形式和非最終形式 2）發音標記 3）元音和 4）音節標記。
在這種情況下，由於您不需要忽略這些屬性之間的差異，您可以使用 100 級排序規則。下面的範例顯示了一個帶有發音標記、音節標記和元音的希伯來字母 (Sin)。有六個版本，以便可以表示組合字元的每個可能的排序組合。第七個條目使用另一個點來創建具有相同基本字母、元音和音節標記的字母 Shin。查詢顯示只有六個“Sin”條目相互匹配（即使字節順序不同），但不匹配“Shin”。
我包括了使用
Latin1_General和Japanese_XJIS_140排序規則，以表明規則也適用於這些規則，以防您需要使用它們（140排序規則，僅在日語中，比舊版本具有更多的大寫/小寫映射）。但總的來說，最好堅持使用希伯來語排序規則，100如果您需要忽略元音、標記、點以及韻母與非韻母形式的差異，請使用非版本。DECLARE @Shin NVARCHAR(5) = NCHAR(0x05E9), -- base Hebrew letter @Dot NVARCHAR(5) = NCHAR(0x05C2), -- Sin Dot @Mark NVARCHAR(5) = NCHAR(0x05A8), -- Cantillation Mark (i.e. trope) @Vowel NVARCHAR(5) = NCHAR(0x05B8); -- Vowel DECLARE @Dot_Mark_Vowel NVARCHAR(20) = @Shin + @Dot + @Mark + @Vowel, @Dot_Vowel_Mark NVARCHAR(20) = @Shin + @Dot + @Vowel + @Mark, @Vowel_Dot_Mark NVARCHAR(20) = @Shin + @Vowel + @Dot + @Mark, @Vowel_Mark_Dot NVARCHAR(20) = @Shin + @Vowel + @Mark + @Dot, @Mark_Vowel_Dot NVARCHAR(20) = @Shin + @Mark + @Vowel + @Dot, @Mark_Dot_Vowel NVARCHAR(20) = @Shin + @Mark + @Dot + @Vowel, @ShinDot_Mark_Vowel NVARCHAR(20) = @Shin + NCHAR(0x05C1) + @Mark + @Vowel; SELECT @Dot_Mark_Vowel AS [Sin], @ShinDot_Mark_Vowel AS [Shin]; ;WITH chr AS ( SELECT * FROM (VALUES (@Dot_Mark_Vowel, 'Dot + Mark + Vowel'), (@Dot_Vowel_Mark, 'Dot + Vowel + Mark'), (@Vowel_Dot_Mark, 'Vowel + Dot + Mark'), (@Vowel_Mark_Dot, 'Vowel + Mark + Dot'), (@Mark_Vowel_Dot, 'Mark + Vowel + Dot'), (@Mark_Dot_Vowel, 'Mark + Dot + Vowel'), (@ShinDot_Mark_Vowel, 'ShinDot + Mark + Vowel') ) tmp([Hebrew], [Description]) ) SELECT chr1.[Hebrew], '--' AS [---], chr1.[Description] AS [Description_1], CONVERT(VARBINARY(20), RIGHT(chr1.[Hebrew], 3)) AS [Bytes_1], '--' AS [---], chr2.[Description] AS [Description_2], CONVERT(VARBINARY(20), RIGHT(chr2.[Hebrew], 3)) AS [Bytes_2] FROM chr chr1 CROSS JOIN chr chr2 WHERE chr1.[Description] <> chr2.[Description] -- do not compare item to itself AND chr1.[Hebrew] = chr2.[Hebrew] COLLATE Hebrew_100_CS_AS_SC AND chr1.[Hebrew] = chr2.[Hebrew] COLLATE Latin1_General_100_CS_AS_SC AND chr1.[Hebrew] = chr2.[Hebrew] COLLATE Japanese_XJIS_140_CS_AS; -- this query returns 30 rows
這取決於很多事情。排序規則是排序、比較和非 unicode 程式碼頁。
這個 repo有一個很好的關於希伯來語的選項列表。
+---------------------------+---------------------------------------------------------------------------------------------------------------------+ | Hebrew_BIN | Hebrew, binary sort | | Hebrew_BIN2 | Hebrew, binary code point comparison sort | | Hebrew_CI_AI | Hebrew, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Hebrew_CI_AI_WS | Hebrew, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive | | Hebrew_CI_AI_KS | Hebrew, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive | | Hebrew_CI_AI_KS_WS | Hebrew, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive | | Hebrew_CI_AS | Hebrew, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Hebrew_CI_AS_WS | Hebrew, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive | | Hebrew_CI_AS_KS | Hebrew, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Hebrew_CI_AS_KS_WS | Hebrew, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive | | Hebrew_CS_AI | Hebrew, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Hebrew_CS_AI_WS | Hebrew, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive | | Hebrew_CS_AI_KS | Hebrew, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive | | Hebrew_CS_AI_KS_WS | Hebrew, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive | | Hebrew_CS_AS | Hebrew, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Hebrew_CS_AS_WS | Hebrew, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive | | Hebrew_CS_AS_KS | Hebrew, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Hebrew_CS_AS_KS_WS | Hebrew, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive | | Hebrew_100_BIN | Hebrew-100, binary sort | | Hebrew_100_BIN2 | Hebrew-100, binary code point comparison sort | | Hebrew_100_CI_AI | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Hebrew_100_CI_AI_WS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive | | Hebrew_100_CI_AI_KS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive | | Hebrew_100_CI_AI_KS_WS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive | | Hebrew_100_CI_AS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Hebrew_100_CI_AS_WS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive | | Hebrew_100_CI_AS_KS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Hebrew_100_CI_AS_KS_WS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive | | Hebrew_100_CS_AI | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Hebrew_100_CS_AI_WS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive | | Hebrew_100_CS_AI_KS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive | | Hebrew_100_CS_AI_KS_WS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive | | Hebrew_100_CS_AS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Hebrew_100_CS_AS_WS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive | | Hebrew_100_CS_AS_KS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Hebrew_100_CS_AS_KS_WS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive | | Hebrew_100_CI_AI_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters | | Hebrew_100_CI_AI_WS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters | | Hebrew_100_CI_AI_KS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters | | Hebrew_100_CI_AI_KS_WS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters | | Hebrew_100_CI_AS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters | | Hebrew_100_CI_AS_WS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters | | Hebrew_100_CI_AS_KS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters | | Hebrew_100_CI_AS_KS_WS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters | | Hebrew_100_CS_AI_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters | | Hebrew_100_CS_AI_WS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters | | Hebrew_100_CS_AI_KS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters | | Hebrew_100_CS_AI_KS_WS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters | | Hebrew_100_CS_AS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters | | Hebrew_100_CS_AS_WS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters | | Hebrew_100_CS_AS_KS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters | | Hebrew_100_CS_AS_KS_WS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters | +---------------------------+---------------------------------------------------------------------------------------------------------------------+