How to add leading padded zeros until 4 characters after that original number should come in SQL Server 2008?

Question

I want to generate running serial no like 0001, 0999, 1100, 19300 with leading padded zeros until four characters. I have written below query to generate that number.

Select Right(Power(10, 4) + 02, 4)

Select Right(Power(10, 4) + 102, 4)

Select Right(Power(10, 4) + 10002, 4)

Actual Result:-

0002

0102

0002

Expected Result:-

0002

0102

10002

In SQL Server 2012, there is FORMAT function available.

SELECT Format(1, '0002')

SELECT Format(1000, '0102')

SELECT Format(10000, '10002')

Actual Output:-

0002

0102

10002

Currently I am using SQL Server 2008. How can I achieve that padded left zeros until 4 characters length after that original number should come?

http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 — Mahmut Ali ÖZKURAN, Sep 30 '16 at 11:23
@MahmutAliÖZKURAN, the linked question accepted answer does not provide my expected output. — RGS, Sep 30 '16 at 11:26
How do you expect `10002` from `Select Right(Power(10, 4) + 10002, 4)` ? — Fredrick Gauss, Sep 30 '16 at 11:37

score 2 · Accepted Answer · answered Sep 30 '16 at 11:31

2

You'll have to work with the value's length:

Select CASE WHEN LEN([your_column]) > 4 THEN [your_column] ELSE Right(Power(10, 4) + [your_column], 4) END

answered Sep 30 '16 at 11:31

Rick Wolff

769
11
25

score 2 · Answer 2 · answered Sep 30 '16 at 11:42

2

Using REPLICATE:

DECLARE @i INT = 10
SELECT CASE 
          WHEN LEN(CAST(@i AS VARCHAR(4))) >=4 THEN CAST(@i AS VARCHAR(4))            
          ELSE REPLICATE('0', 4-LEN(CAST(@i AS VARCHAR(4)))) + CAST(10 AS VARCHAR(4))
       END

answered Sep 30 '16 at 11:42

Giorgos Betsos

71,379
9
63
98

How to add leading padded zeros until 4 characters after that original number should come in SQL Server 2008?

2 Answers2