asked on

Filter Vulgar words and detect only 160 words in Stored Procedure

Trying to filter vulgar words and limit to 160 words in @Message variable before insert statement, and return several strings forout put,

@ExpireDate DATETIME,
 @ReturnString VARCHAR(100) OUTPUT
 
AS  

BEGIN 
     SET NOCOUNT ON 
     
     IF (CHARINDEX('bitch', @Message) > 0) 
     
		@ReturnString = 'Please do not use vulgar words'
     
     ELSE
     

     INSERT INTO dbo.fSMSBuffB
          ( 
            fBatchNo                     ,
            fOrderNo                     ,
            fRefNo                       ,
            fPriority                    ,
            fFromID                      ,
            fMobileNumber                ,
            fMessage					 ,
            fInputDate					 ,
            fSuccessDate				 ,
            fExpireDate        
          ) 
     VALUES 
          ( 
            @BatchNo                      ,
            @OrderNo                      ,
            @Refno                        ,
            @Priority                     ,
            @FromID                       ,
            @MobileNumber                 ,
            @Message                      ,
            @InputDate                    ,
            @SuccessDate                  ,
            @ExpireDate
          ) 

END

Open in new window

Ephraim Wangoya

Best way would be to create a table that will hold the words you wish to filter. Then create a stored procedure that checks for the words.
something very simple like

create table InvalidWords(
	id int identity,
	sword varchar(32)
)
	
------
create procedure ValidateWords @Message varchar(8000), @status int out
as
begin
  declare @id int, @word varchar(32)
  set @status = 0
  set @id = 1
  
  while exists(select 1 from InvalidWords where id >= @id)
  begin
    select @id = ID, @word = sword from InvalidWords where id >= @id
    set @id = @id + 1 
    
    if CHARINDEX(@word, @Message, 1) >= 1
    BEGIN
		set @status = 1
		break
    END
  end
end

Open in new window

Ephraim Wangoya

In your code, you could do

BEGIN
SET NOCOUNT ON

DECLARE @status int
ValidateWords(@Message, @Status)

IF @Status = 1
@ReturnString = 'Please do not use vulgar words'
ELSE
.....................

doramail05

ASKER

guess should be making a table to store and retrieve it,

But what if just without the method,

only by using this stored procedure to filter several words

Ephraim Wangoya

That would become a little bit messy and hard to maintain, think adding a new word to your check.

You will end up with multiple conditions, as many as the words you would like to filter eg

if (CHARINDEX('xxx', @Message, 1) >=1) OR (CHARINDEX('yyy', @Message, 1) >=1) OR ..........

ASKER CERTIFIED SOLUTION

doramail05

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

assist from other
Then why are your not awarding points for assists?

doramail05

ASKER

assist from other