SQL 某個欄位包含 文字、符號及數值，是否可依 數值、文字來排序?

前言

有同事詢問，SQL DB 中某個欄位，資料型態為 nvarchar，裡面存放著數值、文字、符號等內容。
依照預設的排序，結果會是 1, 11, 2, 文字 …

那有辦法，數值時就依數值排序後，再用文字排嗎?
例如 1, 2, 11, 文字 …

研究

建立測試資料

create table t1(
    c1 nvarchar(32)
)
GO

insert into t1(c1) values('1'), ('11'), ('2'), ('3'),('A'), ('AA'), ('ABC'), (N'一'), (N'二'), (';'), (''''), ('B');
select * from t1 order by c1;

結果如下，

研究

要讓它依數值大小排序，就要先將它轉成數值。
可是那文字怎麼辦呢?
分成2部份，再 union all 起來嗎 ?

嗯…

再看一下內容，其實我們只要將數值的內容，依數值排序就可以了。
所以我們就可以用SQL的 TRY_ 開頭的函數，例如 TRY_CONVERT，它會將可以轉成數值字串就會傳回數值，但如果無法轉的就會傳回 null 。
所以我們就可以依 TRY_CONVERT(int, c1) 來排序，這樣那些數值的資料就不會依 字串 來排，而是用 數值 來排。如下，

SELECT *, TRY_CONVERT(int, c1) from t1
order by  TRY_CONVERT(int, c1);

因為文字是 null ，所以文字會先跑出來，後面的數字有依大小來排序。
但是文字卻沒有排序了，例如 A .. 中間插了一些符號後，才會輸出 B，
為了讓文字也可以有排序，所以要再加入原有的欄位來排序，如下，

SELECT *, TRY_CONVERT(int, c1) from t1
order by  TRY_CONVERT(int, c1), c1;

那如果要數值先輸出然後再文字呢?

這時只要將 null 改成 int 的最大值，所以再加入 ISNULL，如下，

SELECT *, TRY_CONVERT(int, c1) from t1
order by  ISNULL(TRY_CONVERT(int, c1), 2147483647) , c1;

這樣數值的字串就會先被輸出哦 :)

可是如果您是 SQL 2008 的話，並沒有 TRY_CONVERT 可以用，這時就需要自已寫一個 function ，可以參考 Simulating TRY_CONVERT() in SQL Server 2008，如下，

CREATE FUNCTION dbo.TryConvertInt
(
  @value nvarchar(4000)
)
RETURNS int
AS
BEGIN
  RETURN (SELECT CONVERT(int, 
    CASE WHEN LEN(@value) <= 11 THEN
      CASE WHEN @value NOT LIKE N'%[^-0-9]%' THEN
        CASE WHEN CONVERT(bigint, @value) BETWEEN -2147483648 AND 2147483647 
             THEN @value 
        END 
      END 
    END));
END
GO	

再來就是將原本使用 TRY_CONVERT 改成使用 dbo.TryConvertInt ，如下，

SELECT *, dbo.TryConvertInt(c1) from t1
order by  ISNULL(dbo.TryConvertInt( c1), 2147483647) , c1;

• 註 1: 如果 效能 有問題的話，請先將資料縮小，或是使用 Computed Column Indexes on Computed Columns
• 註 2: SQL Server 2008/2008 R2 的支援將於 2019 年 7 月 9 日結束