# Thread: How to dictate a specific number format w/ leading zeros

1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213

## Unanswered: How to dictate a specific number format w/ leading zeros

I am trying to output a number in a specific format. I am playing with CAST() and CONVERT() but have not been able to get what I need.

Current: 0.019891
Desired: 000199

It doesn't have to remain in a number format, as i will be output to a CSV in order to bulk load into another system.

2. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
After playing around with it for a while, This gets me what I need, but I am wondering if there is a cleaner, more efficient way:

Code:
select '000'+rtrim(cast(cast(round(0.019891,4)*10000 as int) as char))
EDIT: this doesnt always work because if the starting number has fewer decimals, then there wont be full 6 characters. For example 0.0004 turns into 0004, but should be 000004. Hm....
Last edited by clawlan; 05-16-13 at 13:07.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I would use:
Code:
SELECT Right(Str(1e6 + 1e4 * 0.019891), 6)
-PatP

4. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by Pat Phelan
I would use:
Code:
SELECT Right(Str(1e6 + 1e4 * 0.019891), 6)
-PatP
oh wow, i think that's it. Can you help me understand how this works?

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Your source number is a floating point, so I do all of the math in floating point to avoid forcing data type conversions... They slow things down and can cause all kinds of funky anomalies, so I try to avoid them.

1e6 is scientific notation for one million. 1e4 is scientific notation for 10000. Scientific notation forces the constant to be floating point, which forces the math to be floating point too.

Str() converts the float to a string, and defaults to the format that you want. Right() peels off the right-most portion of the string, which is the only part that you want.

-PatP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•