Add Leading Zeros in Number in SQL

teradata

We so often get the requirement to show numbers in some specific format or to add leading zeros if the length is short.

Let us see an example:

CREATE MULTISET VOLATILE TABLE vt_add_zeros
(
COL1 INTEGER NOT NULL
)
PRIMARY INDEX(COL1)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_add_zeros
SEL 1;
INSERT INTO vt_add_zeros
SEL 10;
INSERT INTO vt_add_zeros
SEL 100;
INSERT INTO vt_add_zeros
SEL 1000;

Now the data may look like:

1
10
100
1000

However if the requirement is the output should always be of 4 digits and add leading zeros to fill.
Then most of us think of below manner to achieve this:

SEL COL1,
CASE
WHEN CHAR_LENGTH(TRIM(COL1))=1 THEN TRIM(‘000’)||TRIM(COL1)
WHEN CHAR_LENGTH(TRIM(COL1))=2 THEN TRIM(’00’)||TRIM(COL1)
WHEN CHAR_LENGTH(TRIM(COL1))=3 THEN TRIM(‘0’)||TRIM(COL1)
WHEN CHAR_LENGTH(TRIM(COL1))=4 THEN TRIM(COL1)
END AS COL2
FROM vt_add_zeros
ORDER BY COL1;

Now this may give us the required result however there is another very simple manner to achieve same result.

SEL COL1, TRIM(COL1 (FORMAT ‘9(4)’)) AS COL2
FROM vt_add_zeros
ORDER BY COL1;

Result:

0001
0010
0100
1000

Leave a Reply

Your email address will not be published. Required fields are marked *