Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question 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. #2
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    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. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would use:
    Code:
    SELECT Right(Str(1e6 + 1e4 * 0.019891), 6)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    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. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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