October 23, 2019 at 12:17 am
Comments posted to this topic are about the item Function to pad Bigint with leading zeros or other single characters
October 31, 2019 at 10:00 am
*reply deleted*
October 31, 2019 at 11:37 am
You would get much better performance by creating an inline Table Valued Valued Function (iTVF).
Something like this
CREATE FUNCTION dbo.fnPadNum (
@Num bigint --Number to be padded
, @sLen tinyint --Total length of results
, @PadChar char(1)
)
RETURNS table WITH SCHEMABINDING
--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"
AS
RETURN SELECT PadNum = RIGHT(REPLICATE( @PadChar, @sLen ) + CAST(ISNULL( @Num, 0 ) AS varchar(20)), @slen);
GO
--Usage:
SELECT PadNum FROM dbo.fnPadNum( 201, 5, '0' );
SELECT PadNum FROM dbo.fnPadNum( 201, 5, '*' );
SELECT PadNum FROM dbo.fnPadNum( 201, 5, ' ' );
-- Used when querying a table
SELECT a.BigNum, pn.PadNum
FROM YourTable AS a
CROSS APPLY dbo.fnPadNum(a.BigNum, 5, '0') AS pn;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy