摘要:[SQL Server] 序號補零至指定長度 function
留個紀錄 .... 找半天
(視情況調整BIGINT)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: Hikaru
-- Create date: 2013-02-21
-- Description: 將 INT 欄位 自動補 0
-- Ver: 2013-02-21
-- Change Log
-- 2013-02-21 Hikaru 初始版本
--
-- Param:
-- INT @argIntNumber = 來源數字。
-- INT @argIntReturn = 回傳字元數。
--
-- =============================================*/
CREATE FUNCTION [dbo].[ZeroFill] (
@argIntNumber INT,
@argIntReturn INT = 7
)
RETURNS NVARCHAR(31)
AS
BEGIN
IF @argIntReturn > 31
BEGIN
SET @argIntReturn = 31
END
ELSE IF @argIntReturn < 0
BEGIN
SET @argIntReturn = 1
END
DECLARE @returnVarchar NVARCHAR(31)
SET @returnVarchar =
CASE WHEN LEN(CAST(@argIntNumber AS VARCHAR)) >= @argIntReturn
THEN CAST(@argIntNumber AS VARCHAR)
ELSE REPLICATE('0', @argIntReturn - LEN(CAST(@argIntNumber AS VARCHAR))) + CAST(@argIntNumber AS VARCHAR)
END
RETURN @returnVarchar
END
