代码之家 › 专栏 › 技术社区 › Roy Tinker

如何在数据库中的所有varchar和n varchar字段之间转换换行符(用\r\n替换为\n)

newline tsql sql-server sql

Roy Tinker · 技术社区 · 14 年前

我正在从我构建的系统中的一个错误中恢复,在这个系统中,我没有考虑到IE生成Windows样式的换行符(\r\n),而其他浏览器在用文本区域发布HTML表单时生成Unix样式的换行符(\n)。现在,我需要在我的SQL Server数据库的varchar和n varchar字段中将所有Windows样式的换行符(\r\n)转换为Unix样式的换行符(\n)。

是否有方法遍历T-SQL中的所有表/行,并将varchar和n varchar字段的'\r\n'实例替换为'\n'?

编辑:我想替换部件应该是

REPLACE(@fieldContents, CHAR(13)+CHAR(10), CHAR(10))

最困难的部分是在所有varchar和nvarchar字段中执行此操作。

2 回复 | 直到 14 年前

Joe Stefanelli 14 年前

像这样?然后可以动态地执行这些字符串,或者只剪切/粘贴结果并在查询窗口中执行它们。

select 'update ' + sc.name + '.' + t.name + ' set ' + c.name + ' = replace(' + c.name + ', CHAR(13)+CHAR(10), CHAR(10))'
from sys.columns c
    inner join sys.systypes st
        on c.system_type_id = st.xtype
            and CHARINDEX('varchar', st.name) <> 0
    inner join sys.tables t
        on c.object_id = t.object_id
    inner join sys.schemas sc
        on t.schema_id = sc.schema_id

Tom H 14 年前

您可以遍历Information_模式中的系统视图,并运行动态SQL来执行此操作。相关视图应该是information_schema.columns。

更好的方法可能是让您的UI在必须显示值时处理它。您是否有一种方法来防止类似的值将来进入数据库?

下面是一些示例代码,可以帮助您开始:

DECLARE
    @table_schema SYSNAME,
    @table_name   SYSNAME,
    @column_name  SYSNAME,
    @cmd          VARCHAR(MAX)

DECLARE cur_string_columns AS
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.COLUMNS
    WHERE
        DATA_TYPE IN ('VARCHAR', 'CHAR') AND  -- NVARCHAR and NCHAR?
        CHARACTER_MAXIMUM_LENGTH > 1

OPEN cur_string_columns

FETCH NEXT FROM cur_string_columns INTO @table_schema, @table_name, @column_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'UPDATE
    ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + '
SET ' + QUOTENAME(@column_name) + ' = REPLACE(' + QUOTENAME(@column_name) + ', CHAR(13) + CHAR(10), CHAR(10))'

    EXEC(@cmd)

    FETCH NEXT FROM cur_string_columns INTO @table_schema, @table_name, @column_name
END

CLOSE cur_string_columns

DEALLOCATE cur_string_columns

如果您有大桌子,这可能需要很长时间才能运行。此外,最好只更新每个表一次,而这将为表中的每个字符串列更新一次。如果我在一个大型数据库上执行此操作,那么我会将脚本更改为相应的脚本-按表架构和表名对光标进行排序,为表中的每一列附加到字符串的集合部分,只有当表更改时才执行(@cmd),然后重置集合字符串。