CODEWITHSUNDEEP
sqlt-sqldynamics-crmdynamics-crm-4
gwc
gwc

Reputation: 171

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?

Upvotes: 2

Views: 7230

Answers (5)

Sebastian Zaklada
Sebastian Zaklada

Reputation: 2376

I just stumbled upon this and I know that it has been asked quite a long time ago, but since I know a different, possibly simpler solution, I decided to share.

NOTE: This will work only for columns that you want to pad contain only numerical characters. This is due to the fact, that the STR() function takes a float as the first parameter and any strings will be converted to float before converting to longer strings again.

Basically - this:

SELECT REPLACE(STR('872', 8), ' ', '0')

will output 00000872

Upvotes: 2

p.campbell
p.campbell

Reputation: 100567

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

Upvotes: 11

onedaywhen
onedaywhen

Reputation: 57023

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;

Upvotes: 0

Russ Cam
Russ Cam

Reputation: 125488

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)

Upvotes: 1

KM.
KM.

Reputation: 103587

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!)

Upvotes: 3

Related Questions