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"
Shane Clarke,
2019-10-31 (first published: 2019-10-23)
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, ' ')
Function to pad bigint with leading 0's or other single character
2019-10-23
1 reads
Function that converts AD UserAccountControl number to details text
2019-04-15 (first published: 2019-04-12)
486 reads
The seventh volume in this collection brings you a compilation of the best articles we've seen over the past year, from over 50 authors.
2022-04-26 (first published: 2013-03-15)
6,945 reads
There's a saying about what it says about a person to assume something. However it's something we all do every day. Phil Factor brings us a guest editorial about assumptions in SQL code.
2020-06-25 (first published: 2009-04-08)
677 reads
It depends. The mantra of many DBAs and others in IT. Steve Jones reminds us why it applies.
2020-06-23 (first published: 2009-03-25)
415 reads