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?

Answer 1

I think this should work:

double.Parse(currencyValue, NumberStyles.Currency);

Here you can see more about the NumberStyles:
http://msdn.microsoft.com/en-us/library/7yd1h1be.aspx

Answer 2

Unless I'm misunderstanding the intent behind your question...

Why not create two fields:

Amount MONEY
CurrencyID INTEGER

These records would look like:

(12.34, 1), (10000000000000000, 3)

CurrencyID is a foreign key into a table that looks like this:

CurrencyID INTEGER PRIMARY KEY NOT NULL
ISOSymbol NCHAR(3) NOT NULL
DisplayName NVARCHAR(50)

For example (1, USD, US Dollar), (2, CAD, Canadian Dollar), ( 3, ZWD, Zimbabwe Dollar)...

Here's a Link to the Authoritative List of ISO 4217 Currency Symbols

Answer 3

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.