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