Implementing SQL function STR

Question

I am writing a piece of code in c# that mimics the behaviour of following SQL function STR in the following case:

declare @number numeric(19,15)    = 219.37345462345234235
SELECT str(@number,9,9) -- 219.37345

declare @number numeric(19,15)    = 19.37345462345234235
SELECT str(@number,9,9) -- 19.373455

Following is an input output table for this scenario:

I have implemented the following function in C# which accepts a double variable and returns a string which is equal to the result returned by the SQL function STR(@number,9,9).

public static string GetCustomStringQuantity(double quantity)
    {
        string customQuantity ;
        int length = (int) Math.Log10(Math.Abs(quantity))+1;
        if (length>9)
            return "*********";
        quantity = Math.Round(quantity, 8 - length,MidpointRounding.AwayFromZero);            
        customQuantity = quantity.ToString();  
        customQuantity= (quantity %1==0)?(customQuantity+".").PadRight(9, '0'):customQuantity.PadRight(9, '0');            

        if (customQuantity.Length > 9)
            customQuantity = customQuantity.Substring(0, 9);
        else if (customQuantity.Length == 9 && customQuantity.EndsWith("."))
            customQuantity = customQuantity.Substring(0, 8);

        return customQuantity; 
    }

How can I make this function better? Or is there a better way to implement this functionality?

Answer 1

Once you have the length of the integer portion, you can use the fx format specifier to get the correct number of decimals, which would clean up the string building part.

 return quantity.ToString("f" + (8 - length));

That should clean up the code some.

As I started typing that, I figured I'd check for edge cases too - since STR is filled with them (eg., it includes the negative sign in the length, returns ** if it overflows, etc.). You're handling the ** case, but a couple of edge-case values fail:

 Input       | Expected  | Actual
 999999999.5 | ********* | 1000000000
 123456789   | 123456789 | ArgOutOfRangeException
 123456789.5 | 123456790 | ArgOutOfRangeException
 -123456789  | ********* | ArgOutOfRangeException

I'd advise that if you're looking for 100% compatibility with STR here, you'll probably want some extensive unit tests.

Here's what I came up with that handles the edge-cases I threw at it, but I don't expect it's bullet proof either. Main changes are using the format specifier, and using .ToString to get the number of digits before the decimal place instead of Log10.

 public static string GetCustomStringQuantity(double quantity) {
    const int LENGTH = 9;
    // Include negative sign, just like STR()
    int numberOfIntDigits = Math.Round(quantity, MidpointRounding.AwayFromZero).ToString().Length;
    if (numberOfIntDigits == LENGTH) return quantity.ToString("f0");
    if (numberOfIntDigits > LENGTH) return new string('*', LENGTH);

    int numberOfDecimals = LENGTH - numberOfIntDigits - 1; // include 1 for the decimal place
    return quantity.ToString("f" + numberOfDecimals); // uses MidpointRounding.AwayFromZero
}