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;

作者: 亂馬客

亂馬客 @叡揚資訊 rainmaker_ho@gss.com.tw https://rainmakerho.github.io/ https://www.slideshare.net/rainmakerho

發表迴響