SQL Server 2016移除VARCHAR中ASCII值，解决INT转换失败问题

解决方案 #

1. 根源分析

报错里的字符串末尾有换行，说明字段中除了双引号，还残留了**CR（ASCII 13）或LF（ASCII 10）**字符——这是因为BULK INSERT指定的ROWTERMINATOR = '0x0A'只识别LF，导致CR留在了字段末尾；同时原CSV的字段是"数字"格式，直接替换引号后，隐藏的换行符仍会干扰INT转换。

2. 优化BULK INSERT参数（减少后续处理）

先调整导入参数，适配CRLF格式和带双引号的字段：

bulk insert #tmp
    From 'C:\acquisition_samples.csv'
    WITH
        (
            CODEPAGE = '65001'
            ,FIRSTROW = 2
            ,FIELDTERMINATOR = '","' -- 匹配CSV中`"val1","val2"`的分隔逻辑
            ,ROWTERMINATOR = '0x0D0A' -- 对应CRLF换行
            ,batchsize=10
            ,TABLOCK
        );

设置FIELDTERMINATOR = '","'后，中间字段会自动去掉前后的双引号，仅首字段开头和尾字段结尾会残留一个双引号，大幅减少后续字符串处理量。

3. 移除特殊字符并转换INT

针对残留的引号、CR/LF，推荐以下两种实用方法：

方法一：用TRIM批量移除指定字符（SQL Server 2016支持）

TRIM可以自定义要移除的字符集合，一步清除双引号、CR、LF：

insert into acquisition_sample(fdc_id_of_sample_food, fdc_id_of_acquisition_food)
select
    -- 移除首字段开头的双引号、CR、LF
    CAST(TRIM('"' + CHAR(13) + CHAR(10) FROM t.fdc_id_of_sample_food) AS INT),
    -- 移除尾字段结尾的双引号、CR、LF
    CAST(TRIM('"' + CHAR(13) + CHAR(10) FROM t.fdc_id_of_acquisition_food) AS INT)
from #tmp t

方法二：嵌套REPLACE移除所有目标字符

如果需要更明确的控制，用嵌套REPLACE逐个移除：

insert into acquisition_sample(fdc_id_of_sample_food, fdc_id_of_acquisition_food)
select
    CAST(
        REPLACE(REPLACE(REPLACE(t.fdc_id_of_sample_food, '"', ''), CHAR(13), ''), CHAR(10), '')
    AS INT),
    CAST(
        REPLACE(REPLACE(REPLACE(t.fdc_id_of_acquisition_food, '"', ''), CHAR(13), ''), CHAR(10), '')
    AS INT)
from #tmp t

方法三：提取纯数字字符（应对复杂杂字符）

如果字段中除了引号和换行，还有其他无关字符，用PATINDEX提取连续数字部分：

insert into acquisition_sample(fdc_id_of_sample_food, fdc_id_of_acquisition_food)
select
    CAST(
        SUBSTRING(t.fdc_id_of_sample_food, 
                  PATINDEX('%[0-9]%', t.fdc_id_of_sample_food), 
                  PATINDEX('%[0-9][^0-9]%', t.fdc_id_of_sample_food + ' ') 
                  - PATINDEX('%[0-9]%', t.fdc_id_of_sample_food) + 1)
    AS INT),
    CAST(
        SUBSTRING(t.fdc_id_of_acquisition_food, 
                  PATINDEX('%[0-9]%', t.fdc_id_of_acquisition_food), 
                  PATINDEX('%[0-9][^0-9]%', t.fdc_id_of_acquisition_food + ' ') 
                  - PATINDEX('%[0-9]%', t.fdc_id_of_acquisition_food) + 1)
    AS INT)
from #tmp t

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