SQL 如何拆分字串中的數值後排序

通常情形下，一列資料都是整體參與排序，但如果當欄位是由字母跟序號組合在一起的複合列，比如現有表TData中列名為NameSerial的一組資料：

Mike105

Rose11

Rose1

Rose5

Mike6

現在需要先按名字排序，再按序號排序。比如Mike6要排在Mike105前面，期望的結果如下：

Mike6

Mike105

Rose1

Rose5

Rose11

 

由於SQL不支援對欄位的單詞拆分，所以只能寫儲存過程拆分資料，然後使用SQL查詢來排序。以SQL Server2012為例，自定義資料拆分標量值函式dbo.words如下：

CREATE FUNCTION dbo.words(@word VARCHAR(100) , @part INT)  RETURNS VARCHAR(100)

AS

BEGIN

    DECLARE @sub VARCHAR(100)

    DECLARE @tmp VARCHAR(10)

    DECLARE @numIndex INT

    DECLARE @len INT

    DECLARE @i INT

    SET @i = 1

    SET @len = Len(@word)

    WHILE @i<=@len

         BEGIN

             SET @tmp = Substring(@word,@i,1)

             IF(ISNUMERIC(@tmp)=1)

                  BEGIN

                      SET @numIndex = @i

                      BREAK

                  END

             SET @i=@i+1

         END

    IF(@part=1)

         BEGIN

             SET @sub=Substring(@word,1,@numIndex-1)

         END

    ELSE

         BEGIN

             SET @sub=Substring(@word,@numIndex,@len-@numIndex+1)

         END

    RETURN @sub

  END

其中引數@part用於指示當前要拆分的部分，1為名字部分，2為序號部分。

需要注意的是，數值部分拆分出來後，要轉換為整數後再排序，否則仍然跟整體排序沒區別。之後的排序，只需要如下SQL即可：

SELECT * FROM TData ORDER BY dbo.words(NameSerial,1),cast(dbo.words(NameSerial,2) as int)

本例雖然透過自定義資料庫函式的方式實現了拆分函式，但並不通用，且不能移植到其他資料庫。

 

這種情況，如果有集算器就方便多了，用SPL實現只要一句話：

connect("db").query("SELECT * FROM TData").sort(NameSerial.words@w())

集算器SPL是解決SQL難題的專業指令碼語言，它語法簡單，符合自然思維，天然分步、層次清晰的程式導向計算語言。它採用與資料庫無關的統一語法，編寫的演算法可在資料庫間無縫遷移。它是桌面級計算工具，即裝即用，配置簡單，除錯功能完善，可設定斷點、單步執行，每步執行結果都可檢視。請參閱

SPL也能很方便地嵌入到JAVA應用，可參考：

具體使用方法可參考：