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

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

---

You can try

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

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

---

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 ...

---

You can use this:

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

The result is string