This blog post will present a T-SQL solution for a 'cleansing' problem. Suppose, we need to remove all commas, single and double quotes from all character fields in a table. The presented stored procedure solves this problem, although for big tables and many characters to remove I will not recommend to use the presented solution as the performance may suffer.

I solved this problem based on the following MSDN thread 

CREATE PROCEDURE dbo.RemoveCharacters(@TableName sysname,
@BadChars NVARCHAR(20))

AS
 BEGIN
  IF OBJECT_ID('TempDB..#Columns','U') IS NOT NULL DROP TABLE #Columns
   SELECT Column_Name INTO #Columns 
   FROM INFORMATION_SCHEMA.COLUMNS C
   WHERE C.TABLE_NAME = @TableName 
   AND C.DATA_TYPE LIKE '%char' -- get list of all character columns in a table
   
   IF @@ROWCOUNT > 0 -- if we don't have char columns, no need to continue
   BEGIN 
   DECLARE @Loop INT, @ReplaceLine NVARCHAR(MAX), @Update NVARCHAR(MAX)
   SET @Loop = 2
   SET @ReplaceLine = 'REPLACE(,' + QUOTENAME(SUBSTRING(@BadChars,1,1),'''') + ','''')' -- construct replace with the placeholder
   WHILE @Loop <= LEN(@BadChars)
     BEGIN 
        SET @ReplaceLine = 'REPLACE(' + @ReplaceLine + ',' + QUOTENAME(SUBSTRING(@BadChars,@Loop,1),'''') + ','''')'
        SET @Loop = @Loop + 1
     END
    PRINT @ReplaceLine 
    SET @Update = ''
    
    SELECT @Update = @Update + ', ' + QUOTENAME(Column_Name) + 
    ' = ' +  REPLACE(@ReplaceLine, '', QUOTENAME(Column_Name))
    FROM #Columns 
    
    SET @Update = 'Update ' + QUOTENAME(@TableName) + '
    SET ' + STUFF(@Update,1,2,'')
    
    PRINT @Update
    
    EXECUTE(@Update)
    END
    END
    GO

EXECUTE dbo.RemoveCharacters 'BadChars', N',"'''                 
    SELECT * FROM dbo.BadChars