0

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

SFish1980
  • 1
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product and string functions are quite vendor specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Mar 03 '22 at 11:13
  • Store the amount in a numeric column, and currency in another column. – jarlh Mar 03 '22 at 12:34

1 Answers1

2

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?

tinazmu
  • 3,880
  • 2
  • 7
  • 20
  • 1
    Thanks so much for the replies. tinazmu that worked perfectly thank you. Apologies for not giving the full information in the first place, will remember next time. Thanks All – SFish1980 Mar 03 '22 at 15:44