Add Leading Zeros in SQL

You can add leading zeros in SQL to number or string by using different functions like to_char. Most common one is to use to_char function with the required format.

You can also use case statement but that requires more effort and is less flexible.

We so often get the requirement to show numbers in some specific format or to add leading zeros to number 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 SELECT 1;
INSERT INTO vt_add_zeros SELECT 10;
INSERT INTO vt_add_zeros SELECT 100;
INSERT INTO vt_add_zeros SELECT 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:

SELECT 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;

Result:

0001
0010
0100
1000

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

SELECT COL1, TRIM(COL1 (FORMAT '9(4)')) AS COL2
FROM vt_add_zeros
ORDER BY COL1;

sql add leading zeros to varchar

You can also concat zeros to varchar and then extract the required number of characters from the string output.
If the database you are working on supports to_char function then you can use to_char function to add leading zeros.

SELECT COL1, 
to_char(col1,'0000') AS COL2
FROM vt_add_zeros
ORDER BY COL1;

--Output
0001
0010
0100
1000

If you prefer watching videos, check our video below: