MS SQL Tips & Tricks

How to pad a number with leading zeros

The easiest way to padd any string with any character in the result of squery is to use REPLCIATE function. The sample bellow shows how to do it. I've used numbers padded with zeros but you can use any also varchar column and padd it with whatever character you need

Numbers Table:

ID Number
1 1
2 334
3 55
4 1455
5 12
6 6
7 12345

declare @ExpectedLenght tinyint;
set @ExpectedLenght=7;

select 
	REPLICATE('0', 
		@ExpectedLenght - 
			len(cast(Number as varchar(max)))
			)+
		cast(Number as varchar(Max)) as PaddedNumber
from Numbers

the result of above query

Number
0000001
0000334
0000055
0001455
0000012
0000006
0012345

The same as above but padded on the right side:

SELECT
declare @ExpectedLenght tinyint;
set @ExpectedLenght=7;

select 
	cast(Number as varchar(Max))+
	REPLICATE('0', 
		@ExpectedLenght - 
			len(cast(Number as varchar(max)))
			)
	as PaddedNumber
		
from Numbers

It will give you result of:

1000000
3340000
5500000
1455000
1200000
6000000
1234500

Download SQL script

Learn more tricks

Add Comment

Comments