CODEWITHSUNDEEP
sql-servert-sqlformatting
Jordan
Jordan

Reputation: 9901

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

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

Upvotes: 11

Views: 41402

Answers (3)

takrl
takrl

Reputation: 6472

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

Upvotes: 5

Mohsen
Mohsen

Reputation: 4266

You can use this:

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

The result is string

Upvotes: 1

bobs
bobs

Reputation: 22184

You can try

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

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

Upvotes: 21

Related Questions