Adding leading zeros to a varchar field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding leading zeros to a varchar field

Author

Topic

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-22 : 11:37:23

Hi Everyone...first of all, thanks in advance for anyone's help. :)

I am wanting to add leading zeros to a varchar field to make it 3-bytes in length. I am using the following syntax, but it is converting all values to '000'.

I am close, but no cigar...

RIGHT(REPLICATE('0',3) + CONVERT(VARCHAR(3),PART1.IPA_NBR),3)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-22 : 11:48:52

This works OK for me. Post the data that you see it failing with.


select
	PART1.IPA_NBR,
	IPA_CHR =
	right(replicate('0',3)+convert(varchar(3),PART1.IPA_NBR),3)
from
	(
	-- Test Data
	select IPA_NBR = 1	union all
	select 22		union all
	select 333
	) PART1

Results:
IPA_NBR     IPA_CHR 
----------- ------- 
1           001
22          022
333         333

(3 row(s) affected)