How to use search and replace for all the columns in a table
I need to have a search and replace function that would search through all the columns in a table and would replace all the comma<,>, double quote<">, and single quotes<'> with space. I have gone through the blogs like http://vyaskn.tripod.com/sql_server_search_and_replace.h开发者_如何学运维tm. But the methods there do not work.
DECLARE @table NVARCHAR(257) = N'dbo.table_name';
DECLARE @sql NVARCHAR(MAX) = N'UPDATE ' + @table + ' SET ';
SELECT @sql = @sql + CHAR(13) + CHAR(10)
+ QUOTENAME(name) + ' = '
+ ' REPLACE(REPLACE(REPLACE('
+ QUOTENAME(name)
+ ','','', '''')'
+ ',''"'', '''')'
+ ',''' + CHAR(39) + CHAR(39) + ''', ''''),'
FROM sys.columns
WHERE system_type_id IN (167, 175, 231, 239)
AND [object_id] = OBJECT_ID(@table);
SET @sql = LEFT(@sql, LEN(@sql)-1) + ';';
PRINT @sql;
--EXEC sp_executesql @sql;
To take out the commas:
update mytable
set myColumn = REPLACE(myColumn, ",", " ")
For the double quote:
update mytable
set myColumn = REPLACE(myColumn, "''", " ")
For the single quote:
update mytable
set myColumn = REPLACE(myColumn, "'", " ")
For more information see: http://msdn.microsoft.com/en-us/library/ms186862.aspx
精彩评论