Post reply

Function to pad Bigint with leading zeros or other single characters

  • October 23, 2019 at 12:17 am

    #3690600

    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

    #3693455

    *reply deleted*

    Nigel
    How to post questions

  • October 31, 2019 at 11:37 am

    #3693480

    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;

    How to post data/code on a forum to get the best help.

Viewing 3 posts - 1 through 2 (of 2 total)

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