The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

T-SQL 技巧:產生「固定長度前面補零的數字字串」

今天看到一則 T-SQL 語法,是用來產生固定長度前面補零的數字字串,例如你拿到一個數值為 123 而你要將其數字轉成 00000123 的字串格式,我剛看到這個語法突然楞了一下,因為看到一個我沒用過的 POWER 函式,我看到這串 T-SQL 後突然聞到一點點怪怪的味道,因此寫出了幾個不同的版本,各位可以參考參考。

以下是我今天看到的寫法:

  • 先將算出 10 的 8 次方 ( 10^8 )
  • 然後加上原本的數字 @Number
  • 運算的結果(float)轉型成 varchar(9) 字串
  • 最後從右邊算起取 8 個字元

針對效能的判斷:

  • 因為 POWER 函式把原本 int 數字型別轉型為 float 型別,較佔記憶體空間
  • 數字轉型 2 次 ( int –> float , float –> varchar )
  • 字串函式處理 1 次
DECLARE @Number INT = 123

DECLARE @Digits INT = 8

DECLARE @Result CHAR(8)

-- 第一種

SELECT @Result =
    RIGHT(
        CAST(
            POWER(10, @Digits) + @Number
            AS VARCHAR(9))
        , @Digits)


SELECT @Result

---

好奇的我我自己重寫如下:

  • 將原本的數字 @Number 轉型成 varchar(8) 後算出資料的長度 ( 注意:不能用 nvarchar 喔!! )
  • 再用 REPLICATE 函式產生特定數量的 '0'
  • 最後再加上原本的數字 @Number 轉型成 varchar(8) 後的字串

針對效能的判斷:

  • 數字轉型 2 次 ( int –> varchar )  → 若多新增一組變數可省去 1 次數值轉型的負擔
  • 數值函式處理 1 次 ( DATALENGTH )
  • 字串函式處理 1 次 ( REPLICATE )
  • 字串相加 1 次
DECLARE @Number INT = 123

DECLARE @Digits INT = 8

DECLARE @Result CHAR(8)

-- 第二種

SELECT @Result =
	REPLICATE('0', 
		@Digits - DATALENGTH(CAST(@Number AS VARCHAR(8)))) +
	CAST(@Number as VARCHAR(8))
	
SELECT @Result

---

最後我到噗浪上詢問網友的看法,果然由 Sean 提供了一個更直覺的寫法:

  • REPLICATE 函式產生固定位數 (@Digits) 的 '0'
  • 將 @Number 轉型成 varchar 字串 (不指定長度)
  • 最後從右邊算起取 8 個字元

針對效能的判斷:

  • 數字轉型 1 次 ( int –> varchar )
  • 字串函式處理 2 次 ( REPLICATE + RIGHT )
  • 字串相加 1 次
DECLARE @Number INT = 123

DECLARE @Digits INT = 8

DECLARE @Result CHAR(8)

-- 第三種

SELECT @Result =
	RIGHT(
		REPLICATE('0', @Digits) + CAST(@Number as VARCHAR)
		, @Digits)

SELECT @Result

---

我不敢說以上三種寫法哪個是好、哪個是壞,在效能上應該不會差多少,畢竟這並非頻繁執行的 T-SQL 語法,但如果以「可讀性」來看,我應該會選擇第三種寫法。

除此之外,網路上也有人(Daniel Read)提供一個寫好的 Function 可供使用,感覺也不錯,當 Function 建立好之後,只要輸入以下 T-SQL 就可以完成使命,可讀性超高! ^_^

SELECT dbo.fnPadLeft('0', 8, 123)

 

相關連結