3

Got sql to pad 0's from: Formatting Numbers by padding with leading zeros in SQL Server

What I want to do is get a users order history from our Navigator database tables. We have a process that puts the orders from the website tables into the the navigator tables (because the orders need to be formatted for the nav table).

I want to query the website tables to get the orders from a logged in user using their user id:

SELECT OrderID FROM db1.zm.dbo.zOrder WHERE AccountID = 631180 (this returns multiple order id's)

db1 is the server, zm is database.

The OrderID's returned are formatted like 4565, 5675, ect. I want to insert them into a temp table like: Z0004565 with a Z and enough leading 0's to hit 7 digits for the number.

How do I modifiy the select statement to do so? Or can I.

SQL for padded 0's: SELECT REPLICATE('0', 7-LEN(4665)) + 4665

SQL Following Comments:

DECLARE @OrderNumTable table (orderNum  varchar(20))

INSERT INTO @OrderNumTable EXEC( SELECT (''Z'' + REPLICATE(''0'', 7-len(OrderID)) + OrderID)FROM db1.zm.dbo.zOrder WHERE AccountID = 631180
Community
  • 1
  • 1
B-M
  • 1,231
  • 1
  • 19
  • 41

2 Answers2

3
SELECT  OrderID,
        'Z'+RIGHT('0000000'+CAST(OrderID AS VARCHAR(7)),7)
FROM db1.zm.dbo.zOrder 
WHERE AccountID = 631180

Updated following the question edit

DECLARE @OrderNumTable table (orderNum  varchar(8))

INSERT INTO @OrderNumTable(orderNum)
SELECT 'Z'+RIGHT('0000000'+CAST(OrderID AS VARCHAR(7)),7)
FROM db1.zm.dbo.zOrder 
WHERE AccountID = 631180
Lamak
  • 69,480
  • 12
  • 108
  • 116
2

Can you not just add a Z to the front?

SELECT 'Z' + REPLICATE('0', 7-LEN(OrderID)) + LTRIM(STR(OrderID))
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Because that will fail if `OrderID` is a numeric data type – Lamak Dec 16 '13 at 20:00
  • @Lamak thought you were referring to `LEN` - fixed to convert the last item to string – D Stanley Dec 16 '13 at 20:07
  • I'm getting this error when trying to do this: Conversion failed when converting the varchar value 'Z000' to data type int. I assume this is because the order id is an int value, but I'm not sure what to do about that because I can't change that. --Added my sql to my question – B-M Dec 16 '13 at 20:09
  • @Brett see my updated answer - needed to convert the last addend to a string. – D Stanley Dec 16 '13 at 20:19