Find and replace bad words stored in database

Question

HI everybody I am beginner in c#i have completed a web application using c#...now i need to replace bad words with another word.where the bad words stored in the database all over the application particularly the user description part....so please give me an Idea

Raaj Kumar · Answer

Hi You can write a storeprocedure(SP) for this and execute it. This SP will look into all the tables in the given database and check for particular entry and replace it with new entry. Please have a look at this SP. CREATE PROC SearchAndReplace ( @SearchStr nvarchar(100) @ReplaceStr nvarchar(100) ) AS BEGIN SET NOCOUNT ON DECLARE @TableName nvarchar(256) @ColumnName nvarchar(128) @SearchStr2 nvarchar(110) @SQL nvarchar(4000) @RCTR int SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%''''') SET @RCTR = 0 WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ) 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName 2) AND TABLE_NAME = PARSENAME(@TableName 1) AND DATA_TYPE IN ('char' 'varchar' 'nchar' 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN SET @SQL= 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ' ' + QUOTENAME(@SearchStr '''') + ' ' + QUOTENAME(@ReplaceStr '''') + ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 EXEC (@SQL) SET @RCTR = @RCTR + @@ROWCOUNT END END END SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome' END After creating execute it and please let me know if you face any issue. Regards raaj

Kirtan Patel · Answer

I m Just Giving you Idea How to Accomplish this .. First you will need List Of Bad Words / textFile. Iterate through each Row of Database Table ..in Which Description are Stores you can Do this By DataReader .. Now Take Description String in in some string Variable ..Replace Bad Words using ..RegEx ..and Update the Description In Database thats it :)

Balaji Essakki · Answer

Thanks a lot

Sam Hobbs · Answer

Note that it might not be as easy as first thought. If you only serach for the bad words alone then you can end up messing up words that are good words. For example the word shell; if you search for the word that is the last four letters of that word then you will end up with sheaven using whatever text you replace the bad word with. I think there are other words like that. So I don't know if Raaj's SP will process the words as words but it can make a difference.