如何将含科学计数法的varchar字段转换为Numeric(12,6)？

我有一张名为APP的表，其中有一个varchar类型字段NUM_AREA，该字段包含以下格式的值：

1.69665589928627E-06 
3.57807129940753E-07
4.77823428809643E-08
1.58324837684631E-12
1.57160684466362E-13
0.0062
10.1595896112714
0.0505828946151305
0.739
0

我需要将这些值转换为Numeric(12,6)类型，尝试使用以下CAST语句：

SELECT CAST (NUM_AREA AS NUMERIC(12,6)) FROM APP

SELECT CAST(CAST(NUM_AREA AS FLOAT) AS NUMERIC(12,6)) FROM APP

但执行后出现错误：

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

请问该如何正确完成转换？

1. 定位脏数据 #

报错的核心原因是NUM_AREA字段中存在无法转换为数值的异常内容（比如空格、非数字字符、空值等）。先找出这些异常记录：

SELECT NUM_AREA 
FROM APP 
WHERE TRY_CAST(NUM_AREA AS FLOAT) IS NULL

清理这些脏数据后，转换才能正常进行。

2. 安全转换目标类型 #

使用TRY_CAST或TRY_CONVERT函数实现安全转换——转换失败时返回NULL，不会中断整个查询，同时完成有效数据的转换：

-- 使用TRY_CAST的写法
SELECT TRY_CAST(TRY_CAST(NUM_AREA AS FLOAT) AS NUMERIC(12,6)) AS CONVERTED_NUM_AREA
FROM APP

-- 或使用TRY_CONVERT的写法
SELECT TRY_CONVERT(NUMERIC(12,6), TRY_CONVERT(FLOAT, NUM_AREA)) AS CONVERTED_NUM_AREA
FROM APP

3. 注意精度截断 #

对于1.57160684466362E-13这类极小值，转换为Numeric(12,6)后会显示为0.000000，因为该类型仅保留6位小数，无法容纳如此小的数值，这属于正常的精度截断。

4. 永久修改字段类型（可选） #

如果需要将字段永久改为Numeric(12,6)，按以下步骤操作：

-- 1. 添加临时字段存储转换后的数据
ALTER TABLE APP ADD NUM_AREA_TEMP NUMERIC(12,6)

-- 2. 更新临时字段
UPDATE APP 
SET NUM_AREA_TEMP = TRY_CAST(TRY_CAST(NUM_AREA AS FLOAT) AS NUMERIC(12,6))

-- 3. 验证数据无误后，删除原字段并重命名临时字段
ALTER TABLE APP DROP COLUMN NUM_AREA
EXEC sp_rename 'APP.NUM_AREA_TEMP', 'NUM_AREA', 'COLUMN'

内容的提问来源于stack exchange，提问作者Luiz Rogério Scudeletti