Convert European numeric format to US

[ProdAir]

Is there a function to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)? I could do replace but that could cause problems and performance issues...

 

[vongrunt]

This "number" - how is it stored? As char/varchar?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[ProdAir]

varchar. I believe the problem lies in the fact the data source are spreadsheets which come from Europe which are then converted into text files. I see no point in converting the data back into spreadsheet format only to re-format the numbers. I haven't found anything on the CONVERT function so I was wondering if there's a better way of doing this other than REPLACE. Thanks!

 

[chiph]

Umm, this may be a dumb question, but why are numbers being stored in a character column?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ProdAir]

Because the conversion to text converts them into characters. Also, this ensures the precision and scale of the numbers are preserved. When importing data from anywhere, it's almost always safest to import everything as characters because you can do your validation against it in a staging area and then make the necessary data type conversion into your production tables.

 

[ProdAir]

Oh, and there are no dumb questions. Thanks for the responses guys.

 

[vongrunt]

AFAIK conversion from string to number always assumes dot (.) for decimal separator and optional comma (,) as thousands separator for money. This is is not affected by collation, SET LANGUAGE, any of CONVERT styles or currency prefixes (€, £, ¥...)

For one-time conversion, REPLACE() won't hurt much.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba