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生成Windows风格的换行符(\ r \ n)和其他浏览器在发布带有文本区域的HTML表单时生成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字段中执行此操作。
2 个解决方案
#1
9
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
#2
1
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部分,仅EXEC(@) cmd)当表更改然后重置您的SET字符串。
#1
9
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
#2
1
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部分,仅EXEC(@) cmd)当表更改然后重置您的SET字符串。