Function to pad Bigint with leading zeros or other single characters

  • Shane Clarke

    SSC-Addicted

    Points: 448

    Comments posted to this topic are about the item Function to pad Bigint with leading zeros or other single characters

  • nigel.

    SSChampion

    Points: 11590

    *reply deleted*

  • DesNorton

    SSC-Insane

    Points: 22812

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply