SQL Server 2019中REPLACE函数处理text类型列报错，转varchar仍无效

问题根源 #

SQL Server的REPLACE函数对text/ntext这类旧版大文本数据类型的支持存在限制：直接调用REPLACE会触发兼容性报错，若仅简单转换为varchar（未指定长度），会默认截断为30字符，导致替换失效或数据丢失。

修正后的脚本 #

DECLARE @DatabaseName NVARCHAR(255) = 'LOTSONLINE';
DECLARE @TableName NVARCHAR(255);
DECLARE @ColumnName NVARCHAR(255);
DECLARE @DataType NVARCHAR(255); -- 新增：存储列数据类型
DECLARE @Sql NVARCHAR(MAX);

-- 游标新增数据类型字段
DECLARE tableCursor CURSOR FOR
SELECT 
    t.TABLE_NAME AS TableName,
    c.COLUMN_NAME AS ColumnName,
    c.DATA_TYPE AS DataType -- 获取列类型
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' 
  AND COLUMNPROPERTY(object_id(t.TABLE_SCHEMA+'.'+t.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity')=0
  AND c.DATA_TYPE IN('varchar', 'nvarchar','text','ntext');

OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = '
        DECLARE @StartTime DATETIME = GETDATE();
        DECLARE @ErrorMessage NVARCHAR(MAX);
        DECLARE @FailedRowNumber INT;
        DECLARE @AffectedRows INT;

        BEGIN TRY
            UPDATE ' + QUOTENAME(@TableName) + '
            SET ' + QUOTENAME(@ColumnName) + ' = ' + 
                CASE 
                    -- 针对text/ntext，转换为max类型后再替换
                    WHEN @DataType IN ('text', 'ntext') THEN 
                        'REPLACE(CAST(' + QUOTENAME(@ColumnName) + ' AS ' + 
                        CASE @DataType WHEN 'text' THEN 'VARCHAR(MAX)' ELSE 'NVARCHAR(MAX)' END + '), ''http://'', ''https://'')'
                    -- 普通字符串类型直接替换
                    ELSE 'REPLACE(' + QUOTENAME(@ColumnName) + ', ''http://'', ''https://'')'
                END + ';
            SET @AffectedRows = @@ROWCOUNT;
            SET @FailedRowNumber = 0;           
        END TRY
        BEGIN CATCH
            SET @ErrorMessage = ERROR_MESSAGE();
            SET @FailedRowNumber = ERROR_LINE();
            -- 输出错误信息便于排查
            PRINT ''错误表: ' + QUOTENAME(@TableName) + ', 列: ' + QUOTENAME(@ColumnName) + ', 错误: '' + @ErrorMessage;
        END CATCH;

        -- 打印执行进度反馈
        PRINT ''处理表: ' + QUOTENAME(@TableName) + ', 列: ' + QUOTENAME(@ColumnName) + ', 影响行数: '' + CAST(@AffectedRows AS NVARCHAR(10)) + '', 耗时: '' + CAST(DATEDIFF(MS, @StartTime, GETDATE()) AS NVARCHAR(10)) + ''ms'';
    ';

    -- 执行动态SQL
    EXEC sp_executesql @Sql;

    FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName, @DataType;
END;

CLOSE tableCursor;
DEALLOCATE tableCursor;

关键修改说明 #

• 新增数据类型判断：游标中获取列的DATA_TYPE，动态生成SQL时区分处理逻辑
• 正确转换大文本类型：将text转换为VARCHAR(MAX)，ntext转换为NVARCHAR(MAX)，避免数据截断
• 增加执行反馈：打印处理的表、列、影响行数和耗时，方便跟踪进度
• 错误信息输出：捕获错误时打印具体表和列的错误详情，快速定位问题

额外建议 #

text/ntext是SQL Server的旧版数据类型，建议逐步迁移到varchar(max)/nvarchar(max)，后者支持更多内置函数且性能更优。

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