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 the 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’. Let us learn Pad Ride Side.

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

Reference: Pinal Dave (https://blog.sqlauthority.com)