Convert to valid decimal data type

Question

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.

Does `'-2.134.630.'` have a dot at the end as well or is that a full stop? — Martin Smith, Oct 10 '11 at 10:28
So, digit grouping symbol is `.`. What is the decimal separator ? — Bogdan Sahlean, Oct 10 '11 at 10:29
Can you specify the behaviour more exactly? Do all values have 3 decimal places, for example. As written, it's not actually clear what the numbers *should* be... — MatBailie, Oct 10 '11 at 10:37
1.324.372 and -2.134.630. which dotted need to be removed any pattern ? — rahularyansharma, Oct 10 '11 at 10:39
@Bogdan Sahlean the behaviour of the data is constant with 3 decimal places but t-clausen.dk answer covers both. — Kip Real, Oct 10 '11 at 11:42

score 1 · Answer 1 · answered Oct 10 '11 at 10:29

1

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

answered Oct 10 '11 at 10:29

Alex K.

171,639
30
264
288

This what I was trying initially – Kip Real Oct 10 '11 at 10:40

t-clausen.dk · Accepted Answer · 2011-10-10T11:03:39.617

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

score 0 · Answer 3 · answered Oct 10 '11 at 10:29

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.

Something like this:

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

Convert to valid decimal data type

3 Answers3

Linked