How do you pad a NVARCHAR field with zeros using T-SQL in a SQL Server 2005 DB?

I have an Orders table that stores the Ordernumber as NVarChar. We manually increment the order number by querying for the biggest order number ordering in descending order and returning the top 1 and then adding 1. We have this implemented in Microsoft CRM 4.0.

e.g Order Numbers (NVarchar)

99
456
32

When I query the above values it returns 99 instead of 456. I want to pad all of the current order numbers to something like 000099 or 000456 using a sql script in SQL server 2005. So the above example would be

000099
000456
000032

What SQL script would I have to write to accomplish this?

Solution

Here's a great padding tutorial on LessThanDot.

DECLARE @Numbers TABLE
(Num INT) 

INSERT @Numbers VALUES('1')
INSERT @Numbers VALUES('12')
INSERT @Numbers VALUES('123')
INSERT @Numbers VALUES('1234')
INSERT @Numbers VALUES('12345')
INSERT @Numbers VALUES('123456')
INSERT @Numbers VALUES('1234567')
INSERT @Numbers VALUES('12345678')   

SELECT RIGHT(REPLICATE('0', 8) + CONVERT(NVARCHAR(8),Num),8) FROM @Numbers

This will result in: