Technical Article

Function to pad Bigint with leading zeros or other single characters

,

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, ' ')

Rate

3 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating