sqltype-conversiondecimalvarchar

Converting a comma-separated varchar to decimal

I have a column in database like;

new_profittl
------------
45,1000
84,0400
156,6500
169,1800
...

My code;

SELECT SUM(CAST(new_profittl as DECIMAL(18,2)))
FROM new_hotelreservation AS R
WHERE DATEADD(hour, DATEDIFF(hour, getUTCdate(), GETDATE()),R.new_salesdate)
      BETWEEN @bas AND @bit AND R.statecode=0

I have to sum this field. The field is NVARCHAR and separated with comma, not dot. so I can't sum this field. I tried to convert or to cast to decimal but it didn't. It gave an error 'Error converting data type nvarchar to numeric.'.

How could I sum this field?

Solution

  • In the short run, this should do the trick:

    sum(cast(replace(new_profitt1, ',', '.') as decimal(18,2)))

    However, I can see any number of comlications coming up from this (multiple commas, no commas, embedded spaces, etc. etc.), so keep an eye on it.