Adding Leading Zeros

Posted on 2003-03-04
Medium Priority
Last Modified: 2012-06-22
Is there a way to add leading zeros in a view in SQL Server?

There does not seem to be the equivalent to "format(myNum,'0000')" in SQL Server.
Question by:access_dude
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

Accepted Solution

hellokns earned 40 total points
ID: 8069544
Try this,
SELECT REPLICATE( '0', 10 - LEN(@a))+@a

this will add a max of 10 leading zeroes.

here @a is varchar. If you have number, convert that to varchar using convert statement.
LVL 12

Expert Comment

ID: 8070051
select Right('0000000000' + Left(123, 10), 10)
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8071901
What datatype is myNum?

If it's VARCHAR, you can do this:

SELECT RIGHT('0000' + myNum, 4)

If it's INT (or other numeric), you can do this:

SELECT RIGHT('0000' + CAST(myNum AS VARCHAR(4)), 4)

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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