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.

  1. DECLARE @Tab AS TABLE  
  2.   
  3. (  
  4.   
  5. Number int  
  6.   
  7. );  
  8.   
  9. INSERT INTO @Tab  
  10.   
  11. SELECT 1 UNION ALL  
  12.   
  13. SELECT 11 UNION ALL  
  14.   
  15. SELECT 120 UNION ALL  
  16.   
  17. SELECT 1345 UNION ALL  
  18.   
  19. SELECT 5000 UNION ALL  
  20.   
  21. SELECT 12300 UNION ALL  
  22.   
  23. SELECT 130001 UNION ALL  
  24.   
  25. SELECT 1400018 UNION ALL  
  26.   
  27. SELECT 19876543 UNION ALL  
  28.   
  29. SELECT 123589753  
  30.   
  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: