In a SQL Server money column how can I deal with different currency notations coming in from country specific Excel files via SSIS (in varchar - transformed to money), taking care of comma and dot representation to make sure the values stay correct?
在SQL Server资金列中，如何处理来自国家特定Excel文件的不同货币符号通过SSIS（在varchar中转换为货币），处理逗号和点表示以确保值保持正确？
For example if these are three column values in Excel:
例如，如果这些是Excel中的三个列值：
22,333.44
22,333.44
22.333,44
22.333,44
22333,44
22333,44
the first notation above will result in 22,3334, which of course is incorrect.
上面的第一个符号将导致22,3334，这当然是不正确的。
What do I need to do with the data? Is it a string replace or something more elegant?
我需要对数据做什么？它是字符串替换还是更优雅的东西？
thank you.
谢谢。
1 个解决方案
#1
2
UPDATED: After discussion in comments the problem has been clarified. The values in the excel column can be of many different regional formats (English using commas to separate thousands and '.' for decimal point, German using '.' for separating thousands and comma for decimal point).
更新：在评论中讨论后，问题已得到澄清。 excel列中的值可以是许多不同的区域格式（英语使用逗号分隔数千，'。'代表小数点，德语使用'。'分隔千位，逗号代表小数点）。
Assuming that the destination format is English and you don't have an accompanying column to indicate the format then you're gonna have to implement a kludge of a workaround. If you can guarantee there will always be 2 numbers after the "decimal place" (comma in german format) then REPLACE(REPLACE(@Value,',',''),'.','') will get rid of every comma/point. Then you will have to get the length of the resulting varchar and manually insert a decimal (or comma) before the last 2 characters. Here's a sample implementation:
假设目标格式是英语，并且您没有附带的列来指示格式，那么您将不得不实施一种变通方法。如果你可以保证在“小数位”之后总是有2个数字（德语格式的逗号）那么REPLACE（REPLACE（@ Value，'，'，''），'。'，''）将摆脱每一个逗号/点。然后，您必须获取生成的varchar的长度，并在最后2个字符之前手动插入小数（或逗号）。这是一个示例实现：
declare @number varchar(12),@trimmednumber varchar(12),@inserteddecimal varchar(12)
set @number='22.333,44'
select @trimmednumber=REPLACE(REPLACE(@number,',',''),'.','')
select @inserteddecimal=(LEFT(@trimmednumber,len(@trimmednumber)-2) + '.' + RIGHT(@trimmednumber,2))
select @number AS [Original],@trimmednumber AS [Trimmed],@inserteddecimal AS [Result]
And the results:
结果如下：
Original Trimmed Result
------------ ------------ ------------
22.333,44 2233344 22333.44
Original Answer:
原答案：
I may be misunderstanding your question but if you take in those values as VARCHAR and insert them into MONEY columns then the implicit conversion should be correct.
我可能误解了您的问题但是如果您将这些值作为VARCHAR并将它们插入到MONEY列中，那么隐式转换应该是正确的。
Here's what I've knocked together to test:
这是我拼凑起来测试的内容：
declare @money_varchar1 varchar(12),@money_varchar2 varchar(12),@money_varchar3 varchar(12)
set @money_varchar1='22,333.44'
set @money_varchar2='22.333,44'
set @money_varchar3='22333,22'
declare @table table (Value money)
insert into @table values (@money_varchar1)
insert into @table values (@money_varchar2)
insert into @table values (@money_varchar3)
select * from @table
And the results:
结果如下：
Value
---------------------
22333.44
22.3334
2233322.00
#1
2
UPDATED: After discussion in comments the problem has been clarified. The values in the excel column can be of many different regional formats (English using commas to separate thousands and '.' for decimal point, German using '.' for separating thousands and comma for decimal point).
更新：在评论中讨论后，问题已得到澄清。 excel列中的值可以是许多不同的区域格式（英语使用逗号分隔数千，'。'代表小数点，德语使用'。'分隔千位，逗号代表小数点）。
Assuming that the destination format is English and you don't have an accompanying column to indicate the format then you're gonna have to implement a kludge of a workaround. If you can guarantee there will always be 2 numbers after the "decimal place" (comma in german format) then REPLACE(REPLACE(@Value,',',''),'.','') will get rid of every comma/point. Then you will have to get the length of the resulting varchar and manually insert a decimal (or comma) before the last 2 characters. Here's a sample implementation:
假设目标格式是英语，并且您没有附带的列来指示格式，那么您将不得不实施一种变通方法。如果你可以保证在“小数位”之后总是有2个数字（德语格式的逗号）那么REPLACE（REPLACE（@ Value，'，'，''），'。'，''）将摆脱每一个逗号/点。然后，您必须获取生成的varchar的长度，并在最后2个字符之前手动插入小数（或逗号）。这是一个示例实现：
declare @number varchar(12),@trimmednumber varchar(12),@inserteddecimal varchar(12)
set @number='22.333,44'
select @trimmednumber=REPLACE(REPLACE(@number,',',''),'.','')
select @inserteddecimal=(LEFT(@trimmednumber,len(@trimmednumber)-2) + '.' + RIGHT(@trimmednumber,2))
select @number AS [Original],@trimmednumber AS [Trimmed],@inserteddecimal AS [Result]
And the results:
结果如下：
Original Trimmed Result
------------ ------------ ------------
22.333,44 2233344 22333.44
Original Answer:
原答案：
I may be misunderstanding your question but if you take in those values as VARCHAR and insert them into MONEY columns then the implicit conversion should be correct.
我可能误解了您的问题但是如果您将这些值作为VARCHAR并将它们插入到MONEY列中，那么隐式转换应该是正确的。
Here's what I've knocked together to test:
这是我拼凑起来测试的内容：
declare @money_varchar1 varchar(12),@money_varchar2 varchar(12),@money_varchar3 varchar(12)
set @money_varchar1='22,333.44'
set @money_varchar2='22.333,44'
set @money_varchar3='22333,22'
declare @table table (Value money)
insert into @table values (@money_varchar1)
insert into @table values (@money_varchar2)
insert into @table values (@money_varchar3)
select * from @table
And the results:
结果如下：
Value
---------------------
22333.44
22.3334
2233322.00