今天看到一則 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)
相關連結