SQL 如何在数据库中的所有 varchar 和 nvarchar 字段之间转换换行符（用 \n 替换 \r\n）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3112747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert newlines (replace \r\n with \n) across all varchar and nvarchar fields in a database
提问by Roy Tinker
I am recovering from a bug in a system I built where I did not take into account that IE generates Windows-style newlines (\r\n) and other browsers generate Unix-style newlines (\n) when posting HTML forms with text areas. Now I need to convert all Windows-style newlines (\r\n) to Unix-style newlines (\n) throughout the varchar and nvarchar fields in my SQL-Server database.
我正在从我构建的系统中的错误中恢复，我没有考虑到 IE 在发布带有文本区域的 HTML 表单时生成 Windows 样式的换行符 (\r\n) 和其他浏览器生成 Unix 样式的换行符 (\n) . 现在我需要在我的 SQL-Server 数据库中的 varchar 和 nvarchar 字段中将所有 Windows 样式的换行符 (\r\n) 转换为 Unix 样式的换行符 (\n)。
Is there a way to iterate through all tables/rows in T-SQL and replace instances of '\r\n' with '\n' for varchar and nvarchar fields?
有没有办法遍历 T-SQL 中的所有表/行并将 '\r\n' 的实例替换为 '\n' 用于 varchar 和 nvarchar 字段？
EDIT: I think the replace part would be something like
编辑：我认为替换部分会像
REPLACE(@fieldContents, CHAR(13)+CHAR(10), CHAR(10))
The hard part is doing this across all varchar and nvarchar fields.
困难的部分是在所有 varchar 和 nvarchar 字段中执行此操作。
回答by Joe Stefanelli
Something like this? You could then dynamically execute these strings or just cut/paste the results and execute them in a query window.
像这样的东西？然后，您可以动态执行这些字符串，或者只是剪切/粘贴结果并在查询窗口中执行它们。
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
回答by Tom H
You could iterate through the system views in INFORMATION_SCHEMA and run dynamic SQL to do it. The relevant view should be INFORMATION_SCHEMA.COLUMNS.
您可以遍历 INFORMATION_SCHEMA 中的系统视图并运行动态 SQL 来执行此操作。相关视图应该是 INFORMATION_SCHEMA.COLUMNS。
A better approach is probably to have your UI deal with it when it has to display the values. Do you have a method to prevent values like that from getting into the DB in the future?
更好的方法可能是让您的 UI 在必须显示值时处理它。你有什么方法可以防止将来像这样的值进入数据库吗？
Here's some sample code that should get you started:
以下是一些示例代码，可以帮助您入门：
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
If you have large tables, this could take a LONG time to run. Also, optimally you would only update each table once, while this will update it once for each string column in the table. If I were doing this on a large database then I would change the script to account for that - order your cursor by the table schema and table name, append to the SET part of the string for each column in the table, only EXEC(@cmd) when the table changes and then reset your SET string.
如果您有大表，这可能需要很长时间才能运行。此外，最好只更新每个表一次，而这将为表中的每个字符串列更新一次。如果我在大型数据库上执行此操作，那么我将更改脚本以解决该问题 - 按表模式和表名对光标进行排序，将表中每一列的字符串的 SET 部分附加到字符串的 SET 部分，仅 EXEC(@ cmd) 表更改时，然后重置您的 SET 字符串。
