?
Solved

Format SQL 2005 View - add 6 zeros in front

Posted on 2014-10-16
2
Medium Priority
?
147 Views
Last Modified: 2014-10-16
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
0
Comment
Question by:allenkent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40385324
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
 

Author Closing Comment

by:allenkent
ID: 40385351
PERFECT!
SELECT RIGHT('00000000' + CAST(numbers as varchar(8)), 8) as my_number
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question