Converting varchar to decimal baseball average

问题:

I uploaded a CSV which automatically converted all my columns to varchar. I need to convert the value 22.30 to 0.223.

alter table badv2018
    alter column [BB Percent] decimal(4, 3) 

But I get the error:

Msg 8115, Level 16, State 8, Line 146
Arithmetic overflow error converting numeric to data type numeric.

回答1:

I need to convert the value 22.30 to 0.223.

You need to devide it by 100.0, then DECIMAL(4, 3) will be OK

DECLARE @Value DECIMAL(4, 3) = 22.3 / 100.0;

SELECT @Value

Returns:

0.223

So, you need to UPDATE your table first, then ALTER the [BB Percent] column.

The easy way is:

• Add new column DECIMAL(4, 3).
• Move the data into it.
• Drop the old column.
• Rename the new one.

---

--First step
ALTER TABLE badv2018
ADD New DECIMAL(4, 3);

--Second step
UPDATE badv2018
SET New = [BB Percent] / 100.0;

--Third step
ALTER TABLE badv2018
DROP COLUMN [BB Percent];

--The last step
EXEC sp_rename 'badv2018.New', 'BB Percent', 'COLUMN';

Enjoy!

Live Demo

---

UPDATE:

You can also add a computed column and leave the [BB Percent] column, this way will ensure you can get the real data and the computed one.

ALTER TABLE badv2018
ADD New AS CAST([BB Percent] / 100.0 AS DECIMAL(4, 3));

回答2:

Make it a wider value. For instance:

alter table badv2018
    alter column [BB Percent] decimal(10, 3) 

(4, 3) can only represent values from 0.000 to 9.999. You probably really want (6, 3), so 10 is overkill.

You can then add another column with the result you want:

alter table badv2018
    add column bb_ratio as ([BB Percent] / 100);