Convert any currency string to double

Question

I need to store multiple currencies in SQL server. I understand that SQL won't support all different types of currencies (unless I store it as a string, but I don't want to do that).

My idea was to convert all the values from their currency format to a standard double and store that instead. Then just re-format based on the culture info when displaying. However, I have tried doing something like e.g.

var cultureInfo = new System.Globalization.CultureInfo("en-US");
double plain = return Double.Parse("$20,000.00", cultureInfo);

This doesn't ever seem to work it always throws a FormatException. Even removing the currency symbol and just trying to do this based on the number alone does the same thing. This is just an example I want to support pretty much any type of currency.

Is there a standard way of stripping out currency and getting the value as a double?

Panathenaea

You don't want to store currency as a double; for instance, you can't store 0.01 accurately. Use a Decimal format. – Spooner

@statiscan - I updated the database not long after this question to use decimals. – Panathenaea

Answer 1

I think this should work:

double.Parse(currencyValue, NumberStyles.AllowCurrencySymbol | NumberStyles.Currency);

Here you can see more about the NumberStyles.

Edit: In case anyone sees this answer without looking at the other answers/comments, this answer answered the question as written, but storing currency as a double is not a good idea, and it would be better to use decimal instead.

Simony

Yeah, or double.Parse(currencyValue, NumberStyles.Currency, cultureInfo) for a given culture. – Soutor

Not an ideal solution, pound sign(for example) won't be parsed if current locale is en-US – Clubwoman

@James, one more thing, NumberStyles.Any includes currency – Clubwoman

I use this in Unversal App: double money; if (double.TryParse(currencyValue, NumberStyles.Currency, System.Globalization.CultureInfo.CurrentCulture, out money)) { } – Merri

I agree with Hans - currencies should be stored as Decimals, which have more precision than doubles - 28 digits versus 15. The reality is that in currency the numbers will never (lacking gross inflation!) go beyond a few trillion and yet you need to still be accurate too the penny when making calculations on those amounts. – Menology

I don't see how this answers the question. How can double.Parse(or decimal.Parse) parse a string that contains any currency symbol? This could be $, ₹,€ or whatever. Currently you are just parsing the current culture's currency symbol. But even if you pass another you would only allow that one. How can you allow any possible culture/currency? That's how i understand this question – Ferrule

Answer 2

You should pass NumberStyles to the Parse function

Decimal.Parse("$20,000.00", NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands, new CultureInfo("en-US"));

A few other things, for currencies I would suggest you use Decimal. And this might be way off, but it might be better to store the currency data as Money in the DB and add a currency code to identify the currency of the value.

Yes, and the answers suggestung NumberStyles.Currency that would be better. It is a pre-Or'd value, if you still think you want to use the strings.

Whalebone

I was currently using float as the DB type? Is this not a good idea? – Panathenaea

@Chris: reason being the column holds other information i.e. not everything stored is monetary, would it still be safe to store non-monetary values using this type? – Panathenaea

@James, so as a rule I do not think that storing different kinds of data in a field is good, but of course I do not know your design and you might have a very good reason. So I offer this advice, I guess you have an indicator that will indicate the data type in the field? I suggest you store the numeric values using a consistent Invariant culture string format and use the indicator to decide you to present the data. So you can always extract the data and convert to numeric/decimal consistently and then make decision about presentation when you need to present the data. – Whalebone

Probably didn't explain my comment properly. All datatypes that are stored in that column are of number value, however, some are just plain integers, where as some might be monetary. The more I think about it the more I think it should be ok to store integers as decimals as I can just round the number up. – Panathenaea

I'm getting an input string was not in correct format while trying to parse £20.00 i changed cultureinfo to en-GB – Faint

Answer 3

You can also use the tryparse()

string input = "$2,000.00";
double parsed = 0d;
double.TryParse(input, NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands, CultureInfo.CurrentCulture, out parsed))

Lumpen

Answer 4

You can use CultureInfo.GetCulture(CultureTypes.AllCultures) to get an array of IFormatProvider objects for each culture.
Additionally, NumberStyles.Currency will allow all the required symbols in a given culture.
Here's a method to parse a currency-formatted string to a double, regardless of the current culture on the environment (allows entry of any culture's currency markers).

public double ParseCurrencyToDouble(string currencyValue)
{
    foreach (CultureInfo info in CultureInfo.GetCultures(CultureTypes.AllCultures))
    {//Looks at every culture in turn until one works.
        if (double.TryParse(currencyValue, NumberStyles.Currency, info, out double result))
        {//Found a matching culture!
            return result;
        }
    }
    //No match found, likely not a currency.
    throw new FormatException(); //Your error handling here
}

Not as efficient as a case where you know the current culture, but you at least cover all bases as the question requires.

And here's a TryParse style implementation:

public bool TryParseCurrencyToDouble(string currencyValue, out double value)
{
    foreach (CultureInfo info in CultureInfo.GetCultures(CultureTypes.AllCultures))
    {//Looks at every culture in turn until one works.
        if (double.TryParse(currencyValue, NumberStyles.Currency, info, out value))
        {//Found a matching culture!
            return true;
        }
    }
    //No match found, likely not a currency.
    return false;
}

Tunicle