Have you ever come across a situation where you need to display numbers in a fixed format? For example, displaying 1 as 0000001, 109 as 0000109, or 0987 as 0000987? If so, you’re in luck! In this article, we will explore different solutions to achieve this in SQL Server.

In a previous article, I discussed the concept of padding numbers with 0 on the right side. Today, I will share three different solutions provided by my readers, each with its own approach and implementation.

Solution 1: Using the RIGHT and REPLICATE Functions

One of the solutions, suggested by Imran Mohammed, involves creating a user-defined function called dbo.FnExample. This function takes two parameters: @Size (the desired width of the number) and @Column_Value (the number to be padded). It uses the RIGHT and REPLICATE functions to add leading zeros to the number.

CREATE FUNCTION dbo.FnExample
(
@Size INT,
@Column_Value VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
SELECT RIGHT(REPLICATE(0, @Size) + CONVERT(VARCHAR(MAX), @Column_Value), @Size) AS ID
)
GO

-- Example usage:
SELECT * FROM dbo.FnExample (7, 123)
GO

Solution 2: Using the LTRIM, RTRIM, and REPLACE Functions

Fenil Desai suggests another approach using the LTRIM, RTRIM, and REPLACE functions. The f_pad_before function takes three parameters: @string (the number to be padded), @desired_length (the desired width of the number), and @pad_character (the character used for padding).

CREATE FUNCTION [dbo].[f_pad_before]
(
@string VARCHAR(255),
@desired_length INTEGER,
@pad_character CHAR(1)
)
RETURNS VARCHAR(255) AS
BEGIN
RETURN LTRIM(RTRIM(
CASE
WHEN LEN(@string) < @desired_length
THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
ELSE @string
END
))
END
GO

-- Example usage:
SELECT dbo.f_pad_before('123', 7, '0')
GO

Solution 3: Using the REPLICATE and CAST Functions

CodeMonkey suggests a third solution using the REPLICATE and CAST functions. The dbo.PadLeft function takes three parameters: @Value (the number to be padded), @PadWidth (the desired width of the number), and @PadChar (the character used for padding).

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

-- Example usage:
SELECT dbo.PadLeft(123, 7, 0)
GO

Regardless of the solution you choose, the output will be ‘0000123’ for all three examples.

I want to congratulate all three of my readers for participating in this interesting puzzle. I encourage other readers to participate in similar puzzles in the future. Stay tuned for more SQL Server concepts and ideas!