转换为有效的十进制数据类型

I have a column [Cash] nvarchar(50) that has data that will later be converted to decimal(9,3) during an import process,some of the data is consistent with normal looking numeric values such as 134.630,-80.662 and 324.372. Occasionally I have data with multiple dots for the numeric values such as 1.324.372 and -2.134.630. Is there a way of removing this extra dot.

我有一个[Cash] nvarchar(50)列,其数据稍后会在导入过程中转换为十进制(9,3),一些数据与正常查看的数值一致,如134.630,-80.662和324.372 。偶尔我有数据值有多个点的数据,如1.324.372和-2.134.630。有没有办法删除这个额外的点。

3 个解决方案

#1

declare @yourtable table(cash varchar(20))
insert @yourtable values('1.324.372')
insert @yourtable values('-2.134.630')
insert @yourtable values('1.234.567.89')

Old Code:

select reverse(replace(replace(stuff(reverse(cash), charindex(
    '.', reverse(cash)), 1, ','), '.', ''), ',', '.')) 
from @yourtable

Slightly upgraded code(result is the same):

稍微升级的代码(结果是相同的):

select reverse(stuff(reverse(replace(cash, '.', '')), 
    charindex('.', reverse(cash)), 1, '.'))
from @yourtable

Result:

1324.372
-2134.630
1234567.89

#2

You could;

select case when len(cash) - len(replace(cash, '.', '')) > 1 then
    reverse(stuff(reverse(cash), charindex('.', reverse(cash)), 1, ''))
else    
    cash
end
from T

#3

Create a view that includes a calculated field with the proper value.

创建一个包含具有适当值的计算字段的视图。

That way you can still see the varchar value and the corresponding decimal value.

这样你仍然可以看到varchar值和相应的十进制值。

Something like this:

像这样的东西:

select [Cash], cast(replace([Cash],'.','') as decimal) as [CashDecimal]
from ....

选择[现金],将[替换([现金],'。','')作为十进制)选择为[CashDecimal] ....

#1

declare @yourtable table(cash varchar(20))
insert @yourtable values('1.324.372')
insert @yourtable values('-2.134.630')
insert @yourtable values('1.234.567.89')