[SQL] SQL column convert to decimal and fix whitespaces

I have got a database which needs fixing, there is a column named FullPrice (nvarcharMAX). Changing data type to decimal is giving following error

arithmetic overflow error converting expression to data type int
​

And when I try to change the properties in Design (SSMS) then I get a timeout error.

I tried to remove all whitespaces and any other special characters.

<Current column> <Desired column>
FullPrice FullPrice
31270 312.70
34040 340.40
41130 411.30
64 98 64.98
68 98 68.98
69 98 69.98
72 97 72.97


Remove whitespaces

UPDATE Table1
SET FullPrice = REPLACE(FullPrice, ' ', '')


Convert column and check if NULL values

Select FullPrice AS FullPrice_s, CASE WHEN Isnumeric(FullPrice) = 1
THEN CONVERT(DECIMAL(6,2),FullPrice)
ELSE 0 END AS FullPrice
From Table1


If you check the screenshot the prices should be like this 1813.78 169.99 etc

Continue reading...