SQL Blog - Pieter van Maasdam, Macaw

SSIS, SSAS, SSRS & other SQL-things I come across...
SQL - How to add leading zeros to a column

I needed to have a column that has a fixed length of 5 characters. The rest had to be filled up with zeros. Example:

SELECT

m.MemberId,

RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, m.MemberId), 5) AS MemberCode

FROM Member m

Output:

MemberId MemberCode

1 00001
2 00002
3 00003
9 00009
10 00010
11 00011

Published 15 November 2007 09:53 by Repieter

Comments

# re: SQL - How to add leading zeros to a column@ 16 November 2007 09:59

It is better to onlt replicate the number of 0s you need. So in your case change 10 to 5.

simonsabin

# re: SQL - How to add leading zeros to a column@ 17 November 2007 11:33

Don't forget this method doesn't deal with negative numbers - it makes them invalid; you get 00-123 instead of -00123.

Tony.

tonyrogerson

# re: SQL - How to add leading zeros to a column@ 19 November 2007 09:06

But if you want to show the data in front end, then I prefer formatting there

Madhivanan

# re: SQL - How to add leading zeros to a column@ 19 November 2007 09:36

Agreed.. It's better to change the number of replications to 5 in this case.

I didn't have to deal with negative numbers here so this will not occur, but thanks for pointing this out.

Repieter

# re: SQL - How to add leading zeros to a column@ 19 November 2007 11:05

Well. Another method

SELECT

m.MemberId,

RIGHT(POWER(10,5)) + m.MemberId, 5) AS MemberCode

FROM Member m

Madhivanan