SQL Server 字串左補零/前導零的高效實現方法

問題背景

SQL Server 缺乏內建 LPAD()/RPAD() 函數，常見需求：流水號、編號固定長度左補零（如 1 → '00001'、333 → '00333'）。

核心技巧：RIGHT('00000' + CAST(@num AS VARCHAR), 5)

DECLARE @SN INT, @SNLen INT = 5;
SET @SN = 1;
SELECT RIGHT(REPLICATE('0', @SNLen) + CAST(@SN AS VARCHAR(10)), @SNLen);  -- '00001'

SET @SN = 333;
SELECT RIGHT(REPLICATE('0', @SNLen) + CAST(@SN AS VARCHAR(10)), @SNLen);  -- '00333'

SET @SN = 55555;
SELECT RIGHT(REPLICATE('0', @SNLen) + CAST(@SN AS VARCHAR(10)), @SNLen);  -- '55555'

關鍵點解析

1. REPLICATE('0', @SNLen)：動態產生指定長度零字串，比寫死 '00000' 更彈性。
2. CAST(@SN AS VARCHAR(10))：數字轉字串，長度足夠容納最大值。
3. RIGHT(..., @SNLen)：取右側固定長度，自動截斷多餘前導零（當數字位數超過目標長度時保留完整數字）。
4. 若數字位數 > 目標長度，結果會超長（如長度 5 的目標存 123456 → '123456'），業務需自行決定是否允許或截斷。

封裝為通用函數

CREATE FUNCTION dbo.fn_LeftPadZero
(
    @Value BIGINT,
    @TotalLength TINYINT
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Str VARCHAR(50) = CAST(@Value AS VARCHAR(50));
    IF LEN(@Str) >= @TotalLength RETURN @Str;
    RETURN RIGHT(REPLICATE('0', @TotalLength) + @Str, @TotalLength);
END;
GO

-- 使用範例
SELECT dbo.fn_LeftPadZero(1, 5)     AS r1;  -- '00001'
SELECT dbo.fn_LeftPadZero(333, 5)   AS r2;  -- '00333'
SELECT dbo.fn_LeftPadZero(55555, 5) AS r3;  -- '55555'
SELECT dbo.fn_LeftPadZero(123456, 5) AS r4; -- '123456' (超長保留原值)

進階：結合年月產生流水號

DECLARE @DateTime DATETIME = GETDATE();
DECLARE @Year CHAR(4) = DATENAME(YEAR, @DateTime);      -- '2024'
DECLARE @Month CHAR(2) = RIGHT('0' + DATENAME(MONTH, @DateTime), 2); -- '01'~'12'
DECLARE @Seq INT = 1;  -- 實際從序號表/SEQUENCE 取得
DECLARE @Serial VARCHAR(20) = @Year + @Month + dbo.fn_LeftPadZero(@Seq, 5);
-- 結果例：'20240100001'
SELECT @Serial;

對比：MySQL/PostgreSQL/Oracle 內建函數

總結

RIGHT(REPLICATE('0', length) + CAST(value AS VARCHAR), length) 是 SQL Server 左補零的標準慣用寫法，效能優異、邏輯清晰、易封裝為函數。配合 DATENAME/SEQUENCE 可快速構建帶年月的業務流水號。記得處理超長數字的業務邏輯（截斷、報錯、擴充長度）。