SQL ntext类型列转numeric/int时出现nvarchar转numeric错误的解决方法

你碰到的错误和列定义的语法书写没有关系，本质是当前reg_no列的存量数据里，存在无法转换为numeric(38,0)类型的非合法数值内容。
你看到的报错提示是“Error converting data type nvarchar to numeric”，是因为这个列最初的ntext类型已经被SQL Server标记为废弃，在之前的操作中已经被隐式转换为nvarchar类型；当执行alter column修改类型时，数据库会逐行尝试把现有内容转成目标的numeric类型，只要有哪怕一行的内容不是可解析的纯数字，就会直接抛出转换错误，终止整个表结构修改操作。

• 第一步：全量筛出无法转换的异常脏数据
  不要用老旧的ISNUMERIC函数做校验，这个函数会把货币符号、特殊字符、科学计数法格式的内容误判为可转换，漏筛真正的异常值，直接用SQL Server原生的TRY_CONVERT函数排查即可，这个函数碰到无法转换的内容不会抛错，只会返回NULL，筛查效率最高：

  -- 列出所有无法转为numeric(38,0)的非空异常行
  SELECT *
  FROM tabl1
  WHERE TRY_CONVERT(numeric(38,0), reg_no) IS NULL
    AND reg_no IS NOT NULL
  

• 第二步：定向处理筛出的异常内容
  按照业务实际场景分两类处理：
  • 如果筛出的内容是无效脏值（比如历史导入的乱码、测试残留的非编号内容）：直接把这些行的reg_no值更新为业务认可的默认值（比如0、或者NULL），确保所有非空的reg_no内容都是可解析的纯数字。
  • 如果筛出的内容只是带了多余不可见字符的有效编号（比如前后带空格、夹杂了换行、制表符等控制字符）：先通过字符串清洗逻辑把内容处理干净，再重新校验：

    -- 清洗前后空格、换行、制表符等常见不可见字符
    UPDATE tabl1
    SET reg_no = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(reg_no)), CHAR(13), ''), CHAR(10), ''), CHAR(9), '')
    WHERE TRY_CONVERT(numeric(38,0), reg_no) IS NULL
      AND reg_no IS NOT NULL
    

  清洗完成后再重复跑第一步的排查SQL，直到查不到任何无法转换的非空行。
• 第三步：执行类型变更
  当全量数据校验通过后，再执行最初的alter语句即可。如果当前表是线上大表，直接执行alter可能会触发长时间锁表、生成大量事务日志影响业务，可以根据数据库版本开启在线表变更选项，减少操作对正常业务的影响。

• 绝大多数遇到这个报错的开发者，都是跳过了存量脏数据校验的环节，直接上来就执行alter语句，被零散的历史脏数据拦住，根本不是类型定义参数写错了。
• 不要为了绕开报错临时把列改成普通varchar类型再慢慢清洗，这样反而会拉长类型迁移的周期，增加后续业务逻辑出问题的概率，正确的顺序一定是先筛脏、洗脏、全量校验通过，再做结构变更。
• 如果排查后发现历史数据里确实存在必须保留的非数值reg_no内容，那说明这个字段本身就不适合用numeric类型存储，应该重新评估字段的类型设计，不要强行转数值导致历史有效数据丢失。

内容的提问来源于stack exchange，提问作者user14085796