, and single quotes<\'> with space. I hav" />
开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜