WinSQL » 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 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.
Posted by Data Geek on 4/25/12 12:10 PM
For DB2, use the || operator instead of +. My data needs to be left zero-padded up to 6 char max length. This DB2 solution works for me:
SELECT RIGHT('000000' || RTRIM(CAST(CAST(col as CHAR(6)) AS VARCHAR(6))), 6) as id1
FROM table
col = 42341
id1 = 042341
col = 5
id1 = 000005
col = 389
id1 = 000389
etc...
The Teradata version is shorter:
SELECT col (format'999999') (char(6)) as id1
FROM table
Posted by chamath on 7/21/12 5:53 AM
this is better
select char(cast(238 as decimal(6,0))) from sysibm.sysdummy1
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 guest on 10/1/13 3:39 AM
I think this is the simplest... it works on DB2:
select digits(dec(id, 4, 0)) from Sample
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?
Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our
public forums to post questions.
