Generate Pad Ride Side of Number With 0

Generate Pad Ride Side of Number With 0:

In this blog I am going to discuss a most frequent issue with numbers and that is leading zero with numbers, or we can say right padding of numbers with zeros.

I have a situation where I need to display the number in fixed format as below.

1 as 000000001

11 as 000000011

120 as 000000120

1234 as 000001234

But the issue is that SQL Server doesn’t contain any inbuilt function that can generate such type of numbers. So we must create our own logic for this.

First we create a table and insert some data into that table.

DECLARE @Tab AS TABLE

(

Number int

);

INSERT INTO @Tab

SELECT 1 UNION ALL

SELECT 11 UNION ALL

SELECT 120 UNION ALL

SELECT 1345 UNION ALL

SELECT 5000 UNION ALL

SELECT 12300 UNION ALL

SELECT 130001 UNION ALL

SELECT 1400018 UNION ALL

SELECT 19876543 UNION ALL

SELECT 123589753

SELECT * FROM @Tab t

Output:

Now we use below query to generate the desired format of number.

Query:

SELECT RIGHT((REPLICATE('0',9)+ CAST( t.Number AS [varchar](9))),9) as Number FROM @Tab t

 

Output: