How do you format a number into a string with padded zeros?

Question

Very simply in SQL Server T-SQL parlance, how do you conver the number 9 to the string N'00009'?

Answer 1

You can try

SELECT RIGHT('00000' + CAST(number AS NVARCHAR), 5)

The result will be a string, not a number type.

Answer 2

If you're on SQL Server 2012 or later, you can also use the FORMAT function:

SELECT FORMAT(1, '00000') AS PaddedNumber

It nicely formats all kinds of stuff ...

Answer 3

You can use this:

SELECT REPLICATE('0',5-LEN('9'))+'9'

The result is string