Function to pad bigint with leading zeros or other single characters
--Sample: "select dbo.fnPadNum(201,5,'0')" returns "00201"
--Sample: "select dbo.fnPadNum(201,5,'*')" returns "**201"
--Sample: "select dbo.fnPadNum(201,5,' ')" returns " 201"
Function to pad bigint with leading zeros or other single characters
--Sample: "select dbo.fnPadNum(201,5,'0')" returns "00201"
--Sample: "select dbo.fnPadNum(201,5,'*')" returns "**201"
--Sample: "select dbo.fnPadNum(201,5,' ')" returns " 201"
CREATE FUNCTION fnPadNum ( @Num BIGINT --Number to be padded , @sLen BIGINT --Total length of results , @PadChar VARCHAR(1) ) RETURNS VARCHAR(20) AS --Pads bigint with leading zeros or other single characters --Sample: "select dbo.fnPadNum(201,5,'0')" returns "00201" --Sample: "select dbo.fnPadNum(201,5,'*')" returns "**201" --Sample: "select dbo.fnPadNum(201,5,' ')" returns " 201" BEGIN DECLARE @Results VARCHAR(20) SELECT @Results = CASE WHEN @sLen > len(ISNULL(@Num, 0)) THEN replicate(@PadChar, @sLen - len(@Num)) + CAST(ISNULL(@Num, 0) AS VARCHAR) ELSE CAST(ISNULL(@Num, 0) AS VARCHAR) END RETURN @Results END GO --Usage: SELECT dbo.fnPadNum(201, 5, '0') SELECT dbo.fnPadNum(201, 5, '*') SELECT dbo.fnPadNum(201, 5, ' ')