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.

  1. DECLARE @Tab AS TABLE
  3. (
  5. Number int
  7. );
  9. INSERT INTO @Tab
  11. SELECT 1 UNION ALL
  13. SELECT 11 UNION ALL
  15. SELECT 120 UNION ALL
  17. SELECT 1345 UNION ALL
  19. SELECT 5000 UNION ALL
  21. SELECT 12300 UNION ALL
  23. SELECT 130001 UNION ALL
  25. SELECT 1400018 UNION ALL
  27. SELECT 19876543 UNION ALL
  29. SELECT 123589753
  31. SELECT * FROM @Tab t

Output:


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

Query:

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

Output: