SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

I had written article SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display few days ago. Previous article was answer to the email which I had received:

Dear Pinal,

I have situation where I need to display my numbers in fixed format. Like

1 as 0000001
109 as 0000109

0987 as 0000987

Is there any string function in SQL Server which can do this?

I had asked my blog readers to participate at the end of the article and I have received three very good suggestions. I am going to list all the three suggestions here. For me all the three suggestions are equally good. I suggest my readers to use any suggestion which they find easy to understand. I suggest to read my previous article before continuing this article further.

Solution 1 by Imran Mohammed

CREATE FUNCTION dbo.FnExample
(
@Size INT,
@Column_Value VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT RIGHT(REPLICATE(0, @Size)
+
CONVERT (VARCHAR(MAX), @Column_Value), @Size) AS ID
)
GO
-- Run following script to see output
SELECT * FROM dbo.FnExample (7, 123)
GO

Solution 2 by Fenil Desai

CREATE FUNCTION [dbo].[f_pad_before]
( @string VARCHAR(255),
@desired_length INTEGER,
@pad_character CHAR(1))
RETURNS VARCHAR(255) AS
BEGIN
/* Prefix the required number of spaces to bulk up the string and
then replace the spaces with the desired character */
RETURN LTRIM(RTRIM(
CASE
WHEN LEN(@string) < @desired_length
THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
ELSE @string
END
))
END
GO
-- Run following script to see output
SELECT dbo.f_pad_before('123',7,'0')
GO

Solution 3 by CodeMonkey

CREATE FUNCTION dbo.PadLeft
(@Value INT, @PadWidth INT, @PadChar CHAR(1))
RETURNS VARCHAR(255)
AS
BEGIN
RETURN
ISNULL(REPLICATE(@PadChar, @PadWidth - LEN(@Value)), '')
+
CAST(@Value AS VARCHAR)
END
GO
-- Run following script to see output
SELECT dbo.PadLeft(123,7,0)
GO

Answer of all of the above script will be ‘0000123’.

Again, I want to congratulate all three of my reader to participate in this interesting puzzle and would like to encourage other readers to participate in similar puzzles like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

10 thoughts on “SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

  1. When calling the function with less than the length: (123,7,0)

    Solution 1 truncates the value.
    Solution 2 and 3 return the value itself.

    I am surprised noone used STUFF():
    CREATE FUNCTION dbo.a
    (
    @Length TINYINT,
    @Pad VARCHAR(1),
    @Column_Value INT
    )
    RETURNS VARCHAR(255)
    AS
    BEGIN
    RETURN STUFF
    (
    REPLICATE(@Pad, @Length),
    @Length – LEN(@Column_Value),
    LEN(@Column_Value),
    @Column_Value
    );
    END
    GO

    select dbo.a(7, 0, 123);
    drop function dbo.a

    Like

  2. Pingback: SQL SERVER - Leading Zero to Number Journey to SQL Authority with Pinal Dave

  3. Hi,
    This is my query for the same.

    use AdventureWorks
    go
    alter procedure MYProc @value1 bigint
    as
    begin
    declare @String1 varchar(10)
    Declare @length bigint
    Declare @TotalLength bigint
    set @TotalLength = 10

    set @length = @TotalLength – LEN(@value1)
    set @String1 = REPLICATE (0,@length) + CONVERT (nvarchar(10),@value1)
    print @String1
    end
    go

    Exec Myproc 555

    Like

  4. my requirement is

    I am trying to figure out how to do looping in SQL.
    I have table like below:

    ID System Material
    1 Floor Wood
    1 Floor Carpet
    1 Wall Dry wall
    1 Wall Paper
    2 Floor Wood
    2 Floor Carpet

    I ‘d like to end up with results like:
    ID System Material
    1 Floor Wood, Carpet
    1 Wall Dry Wall, Paper
    2 Floor Wood, Carpet

    For this i need user defined function

    Please give query

    Like

  5. create function dbo.HirsPadding
    (
    @size int,
    @input varchar(max)
    )
    returns varchar(max)
    as
    begin
    IF @size < LEN(@input)
    RETURN RIGHT(@input,@size)
    return REPLICATE(0,@size-LEN(@input))+@input
    end

    select dbo.HirsPadding(10,'12345')

    Like

  6. Pingback: SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Removing Leading Zeros From Column in Table « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s