Replace character in EUR amount

Question

First post so hope I include the right stuff, apologies if not.

I have a mix of currency amounts Im importing into a database. Some of them are EUR and as such swap the "," and "." positions for decimal and 1,000 separator. I need to replace the "," to a "." for the cents but cant find the right replace/stuff method to do it.

For example I have 10.000,00 EUR I need it to read 10,000.00 EUR I have updated the field so the "," is fixed. Any suggestions appreciated. Thanks

Answer 1

If we assume that you are talking about Microsoft SqlServer and the amount data stored on char/varchar column: AmountCol in the specified format: '10.000,00' and you want to retrieve this data in the other format, you can use the REPLACE function:

SELECT REPLACE(REPLACE(REPLACE(AmountCol,'.','^'),',','.'),'^',',') FROM YourTable

The use of the '^' symbol is arbitrary, to make sure that we don't convert '.' with ',' and then all ',' chars to '.', ending up '10.000.00'. If the assumptions above are wrong you need to provide more detail, like which database you are using, what medium you are importing from, what tool do you use to import, what is the datatype of the target column, etc?