Format SQL 2005 View - add 6 zeros in front

I have a SQL 2005 server and I have value that is a number. The number can be 1 or 2 or 3 digits maximum BUT I need the number to be 8 digits leading zeros for an export system.

Example:  
My numbers:
3
12
120

What I need in my sql view:  (notice it is 8 characters with leading zeros)
00000003
00000012
00000120

Here is my basic statement:
Select Invoices.InvoiceQuantity
FROM dbo.Invoices
allenkentAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No can do as a numeric data type, as they do not support leading zeros.

The only way to pull that off is to convert it to a char with leading zeros, and use RIGHT to handle the number of zeros correctly, in the set that displays it.
For a demo, copy-paste and execute the below into your SSMS
CREATE TABLE #tmp (numbers int) 

INSERT INTO #tmp (numbers) 
VALUES (3), (12), (120) 

SELECT RIGHT('00000000' + CAST(numbers as varchar(8)), 8) as my_number 
FROM #tmp

Open in new window

0
 
allenkentAuthor Commented:
PERFECT!
SELECT RIGHT('00000000' + CAST(numbers as varchar(8)), 8) as my_number
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.