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

Question

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?

Answer 1

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:

00000001
00000012
00000123
00001234
00012345
00123456
01234567
12345678

Answer 2

don't do it this way! store the values as INTs. You'll be forever trapped padding with zeros if you use strings. If you decide to store the zeros in the strings, you'll still have to pad user input with zeros to query for it (or use like '%'+@x+'%', yikes!)

Answer 3

You could CAST the NVARCHARs to INTs and then ordering will work as you want. I'm assuming that Order Numbers contain only numerical characters.

ORDER BY
    CAST(OrderNumber AS INT)

Answer 4

This is basically a re-write of the answer given by pcampbell but using functions that I've found port to other SQL product more easily: CAST rather than CONVERT etc:

SELECT CAST(REVERSE(CAST(REVERSE(CAST(Num AS NVARCHAR(9))) + '000000000' AS NCHAR(9))) AS NVARCHAR(9))
       AS value_padded
 FROM Numbers;