46

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?

James
  • 80,725
  • 18
  • 167
  • 237
  • 4
    You don't want to store currency as a double; for instance, you can't store 0.01 accurately. Use a Decimal format. – staticsan Aug 25 '10 at 03:54
  • @statiscan - I updated the database not long after this question to use decimals. – James Aug 25 '10 at 07:43

3 Answers3

112

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.

Hans Olsson
  • 54,199
  • 15
  • 94
  • 116
  • 7
    Yeah, or `double.Parse(currencyValue, NumberStyles.Currency, cultureInfo`) for a given culture. – Noldorin May 02 '10 at 14:15
  • 2
    Not an ideal solution, pound sign(for example) won't be parsed if current locale is en-US – illegal-immigrant Dec 09 '11 at 09:50
  • @James, one more thing, NumberStyles.Any includes currency – illegal-immigrant Dec 09 '11 at 09:52
  • 3
    I use this in Unversal App: double money; if (double.TryParse(currencyValue, NumberStyles.Currency, System.Globalization.CultureInfo.CurrentCulture, out money)) { } – Petr Voborník Oct 29 '14 at 13:04
  • 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. – Daniel Williams Dec 14 '15 at 15:03
  • 2
    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 – Tim Schmelter Jan 21 '19 at 11:02
23

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.

Chris Taylor
  • 52,623
  • 10
  • 78
  • 89
  • 1
    I was currently using `float` as the DB type? Is this not a good idea? – James May 02 '10 at 14:34
  • @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? – James May 02 '10 at 14:45
  • @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. – Chris Taylor May 02 '10 at 15:13
  • 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. – James May 02 '10 at 15:16
  • I'm getting an input string was not in correct format while trying to parse £20.00 i changed cultureinfo to en-GB – RyanN1220 Apr 18 '19 at 09:42
1

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))
hogarth45
  • 3,387
  • 1
  • 22
  • 27