我們需要對通常是需要“自然”排序的數字和字母混合字元串的值進行一些報告。諸如“P7B18”或“P12B3”之類的東西。@字元串主要是字母序列，然後是數字交替。不過，這些片段的數量和每個片段的長度可能會有所不同。

我們希望這些數字部分按數字順序排序。顯然，如果我直接用 處理這些字元串值ORDER BY，那麼“P12B3”將出現在“P7B18”之前，因為“P1”早於“P7”，但我希望反過來，因為“P7”自然在前面“P12”。

我還希望能夠進行範圍比較，例如@bin < 'P13S6'或類似的。我不必處理浮點數或負數；這些嚴格來說是我們正在處理的非負整數。字元串長度和段數可能是任意的，沒有固定的上限。

在我們的例子中，字元串大小寫並不重要，但如果有一種方法可以以一種可辨識的方式執行此操作，其他人可能會發現這很有用。所有這一切中最醜陋的部分是我希望能夠在WHERE子句中進行排序和範圍過濾。

如果我在 C# 中執行此操作，這將是一項非常簡單的任務：進行一些解析以將 alpha 與數字分開，實現 IComparable，然後您就基本上完成了。當然，SQL Server 似乎沒有提供任何類似的功能，至少據我所知。

任何人都知道使這項工作的任何好技巧？是否有一些鮮為人知的能力來創建實現 IComparable 並使其行為符合預期的自定義 CLR 類型？我也不反對 Stupid XML Tricks（另請參見：列表連接），並且我在伺服器上也提供了 CLR 正則表達式匹配/提取/替換包裝函式。

編輯： 作為一個更詳細的例子，我希望數據表現得像這樣。

SELECT bin FROM bins ORDER BY bin

bin
--------------------
M7R16L
P8RF6JJ
P16B5
PR7S19
PR7S19L
S2F3
S12F0

即將字元串分解為所有字母或所有數字的標記，並分別按字母或數字對它們進行排序，最左邊的標記是最重要的排序項。就像我提到的，如果您實現 IComparable，在 .NET 中小菜一碟，但我不知道您如何（或是否）可以在 SQL Server 中執行此類操作。這肯定不是我在 10 年左右的工作中遇到過的事情。

想要一種明智、有效的方法將字元串中的數字排序為實際數字嗎？考慮為我的 Microsoft Connect 建議投票：支持“自然排序”/DIGITSASNUMBERS 作為排序選項

---

沒有簡單的內置方法可以做到這一點，但有一種可能性：

通過將字元串重新格式化為固定長度的段來規範化字元串：

• 創建一個類型為 的排序列VARCHAR(50) COLLATE Latin1_General_100_BIN2。可能需要根據段的最大數量及其潛在的最大長度來調整最大長度 50。

• 雖然規範化可以在應用程序層更有效地完成，但使用 T-SQL UDF 在數據庫中處理這將允許將標量 UDF 放入AFTER [or FOR] INSERT, UPDATE觸發器中，這樣您就可以保證正確設置所有記錄的值，即使是那些通過即席查詢等進入。當然，標量 UDF 也可以通過 SQLCLR 處理，但需要對其進行測試以確定哪個實際上更有效。**

• UDF（無論是在 T-SQL 還是 SQLCLR 中）應該：

  • 通過讀取每個字元並在類型從字母切換到數字或從數字切換到字母時停止來處理未知數量的段。
  • 對於每個段，它應該返回一個固定長度的字元串，該字元串設置為任何段的最大可能字元/數字（或者可能是 max + 1 或 2 以考慮未來的增長）。
  • Alpha 段應左對齊並用空格右填充。
  • 數欄位應右對齊並左填充零。
  • 如果字母字元可以混合大小寫，但排序需要不區分大小寫，則將該UPPER()函式應用於所有段的最終結果（這樣只需執行一次，而不是每個段）。考慮到排序列的二進制排序規則，這將允許正確排序。

• AFTER INSERT, UPDATE在呼叫 UDF 設置排序列的表上創建觸發器。為了提高性能，使用UPDATE()函式判斷這個code列是否偶數在語句的SET子句中UPDATE（RETURN如果為false），然後在code列上joinINSERTED和DELETED偽表，只處理code值發生變化的行. 請務必指定COLLATE Latin1_General_100_BIN2該 JOIN 條件，以確保確定是否有更改的準確性。

• 在新的排序列上創建一個索引。

例子：

P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

在這種方法中，您可以通過以下方式進行排序：

ORDER BY tbl.SortColumn

您可以通過以下方式進行範圍過濾：

WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

要麼：

DECLARE @RangeStart VARCHAR(50),
       @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
      @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

ORDER BY和過濾器都WHERE應該使用SortColumn由於排序規則而定義的二進制排序規則。

仍然會在原始值列上進行相等比較。

---

其他想法：

• 使用 SQLCLR UDT。這可能會奏效，但與上述方法相比，它是否提供淨收益尚不清楚。

是的，SQLCLR UDT 可以使用自定義算法覆蓋其比較運算符。這可以處理將值與已經是相同自定義類型的另一個值或需要隱式轉換的值進行比較的情況。這應該處理條件中的範圍過濾器WHERE。

關於將 UDT 排序為正常列類型（不是計算列），這只有在 UDT 是“字節排序”時才有可能。“字節排序”意味著 UDT 的二進製表示（可以在 UDT 中定義）自然地以適當的順序排序。假設二進製表示的處理方式與上述 VARCHAR(50) 列的方法類似，該列具有已填充的固定長度段，則符合條件。或者，如果不容易確保二進製表示自然地以正確的方式排序，您可以公開 UDT 的方法或屬性，該方法或屬性輸出一個正確排序的值，然後PERSISTED在其上創建一個計算列方法或屬性。該方法需要是確定性的並標記為IsDeterministic = true。

這種方法的好處是：

• 不需要“原始值”欄位。

• 無需呼叫 UDF 來插入數據或比較值。假設ParseUDT 的方法接受P7B18值並轉換它，那麼您應該能夠簡單地將值自然地插入為P7B18. 並且使用 UDT 中設置的隱式轉換方法，WHERE 條件還允許簡單地使用 P7B18`。這種方法的後果是：

• 如果使用按字節排序的 UDT 作為列數據類型，只需選擇該欄位即可返回二進製表示。或者，如果PERSISTED在 UDT 的屬性或方法上使用計算列，那麼您將獲得該屬性或方法返回的表示。如果您想要原始P7B18值，則需要呼叫編碼為返回該表示的 UDT 的方法或屬性。由於無論如何您都必須重寫該ToString方法，因此這是提供此方法的不錯選擇。

• 目前還不清楚（至少現在對我來說，因為我沒有測試這部分）對二進製表示進行任何更改會有多容易/困難。更改儲存的、可排序的表示可能需要刪除並重新添加該欄位。此外，如果以任何一種方式使用，刪除包含 UDT 的程序集都會失敗，因此您需要確保程序集中除了此 UDT 之外沒有其他任何內容。您可以ALTER ASSEMBLY替換定義，但有一些限制。

另一方面，該VARCHAR()欄位是與算法斷開連接的數據，因此它只需要更新列。如果有數千萬行（或更多），那麼可以通過批處理方法完成。

• 實現實際上允許進行這種字母數字排序的ICU庫。雖然功能強大，但該庫僅提供兩種語言：C/C++ 和 Java。這意味著您可能需要進行一些調整以使其在 Visual C++ 中工作，或者使用IKVM將 Java 程式碼轉換為 MSIL 的可能性很小。該站點上鍊接的一兩個 .NET 輔助項目提供了可以在託管程式碼中訪問的 COM 介面，但我相信它們已經有一段時間沒有更新了，我也沒有嘗試過。最好的辦法是在應用層中處理這個問題，目標是生成排序鍵。然後將排序鍵保存到新的排序列中。

這可能不是最實用的方法。不過，有這樣的能力存在，還是很酷的。我在以下答案中提供了一個更詳細的範例：

是否有排序規則按以下順序對以下字元串進行排序 1,2,3,6,10,10A,10B,11？

但是該問題中處理的模式要簡單一些。有關顯示此問題中處理的模式類型也適用的範例，請轉到以下頁面：

ICU零食展示

在“設置”下，將“數字”選項設置為“開”，其他所有選項都應設置為“預設”。接下來，在“排序”按鈕的右側，取消選中“差異強度”選項並選中“排序鍵”選項。然後將“輸入”文本區域中的項目列表替換為以下列表：

P12B22
P7B18
P12B3
as456456hgjg6786867
P7Bb19
P7BA19
P7BB19
P007B18
P7Bb20
P7Bb19z23

點擊“排序”按鈕。“輸出”文本區域應顯示以下內容：

as456456hgjg6786867
   29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
P7B18
   47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P007B18
   47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P7BA19
   47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19
   47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
P7BB19
   47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19z23
   47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
P7Bb20
   47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
P12B3
   47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
P12B22
   47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .

請注意，排序鍵是多個欄位的結構，以逗號分隔。每個欄位都需要獨立排序，如果需要在 SQL Server 中實現，則需要解決另一個小問題。

---

**如果對使用使用者定義函式的性能有任何顧慮，請注意建​​議的方法對它們的使用最少。事實上，儲存標準化值的主要原因是避免為每個查詢的每一行呼叫一個 UDF。在主要方法中，UDF 用於設置 的值，並且SortColumn僅通過觸發器完成。選擇值比插入和更新更常見，並且某些值永遠不會更新。對於在子句中使用 for a range 過濾器的每個查詢，每個 range_start 和 range_end 值只需要一次 UDF 即可獲得標準化值；UDF 不稱為每行。INSERT``UPDATE``SELECT``SortColumn``WHERE

對於 UDT，其用法實際上與標量 UDF 相同。意思是，插入和更新將每行呼叫一次規範化方法來設置值。然後，規範化方法將在範圍過濾器中的每個 range_start 和 range_value 的每個查詢中呼叫一次，但不是每行。

支持完全在 SQLCLR UDF 中處理規範化的一點是，鑑於它沒有進行任何數據訪問並且是確定性的，如果它被標記為IsDeterministic = true，那麼它可以參與並行計劃（這可能有助於INSERTandUPDATE操作），而T-SQL UDF 將阻止使用並行計劃。

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