RGS RGS - 2 months ago 12
SQL Question

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

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?

Answer

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
Comments