. Quick Links

Knowledge Base

Document information

Document ID:	782
Subject:	Padding leading zeros to an integer
Creation date:	7/29/09 3:27 PM
Last modified on:	12/10/11 4:41 AM

Details

Assume that you have an integer in your database and want to pad with leading zeros to make it a fixed length column.

The table design is as follows.

create table Sample(id integer)

There are 4 rows in this table containing following values:
8
80
800
8000

Our goal is to write a query that will display the following results.
0008
0080
0800
8000

Following query will produce the results you like. This sample is according to the syntax understood by Microsoft SQL Server. Modify the functions appropriately if you are using any other database.

select CASE LEN(CAST(id as VARCHAR(5)))
    WHEN 0 THEN '0000'
    WHEN 1 THEN '000' + CAST(id as VARCHAR(5))
    WHEN 2 THEN '00' + CAST(id as VARCHAR(5))
    WHEN 3 THEN '0' + CAST(id as VARCHAR(5))
    else CAST(id as VARCHAR(5))
    END
From Sample

User comments

Posted by asm on 11/29/11 6:04 AM

select values in to @i then use ... set @ID = 'IN'+ REPLICATE('0',11 - LEN(@i)) + cast(@i as varchar (12))

Posted by The Sinister Minister on 4/26/11 1:25 PM

I find that the case method can become cumbersome when dealing with longer number strings. I tend to use something like this: SELECT RIGHT('00000' + CAST(id as VARCHAR(5)), 5) FROM Sample This is (again) in SQL Server format. For DB2, this would work: SELECT RIGHT('00000' + RTRIM(CAST(CAST(id as CHAR(5)) AS VARCHAR(5))), 5) FROM Sample DB2 (v8 anyway) does not allow casting from INTEGER directly to VARCHAR so we have to pre-cast it to CHAR and then RTRIM to remove the trailing spaces.

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.

Your name:
Your email:	Hide my email address
Verification code:	Enter the verification code you see above more submitting your tip
Tip:	Please limit tips to 1000 characters

Site map | Contact | Products | Support