• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Filter vulgar words and limit to 160 stored procedure

Having a table that contains vulgar words,

trying to write a stored procedure that filter a parameter @Message from vulgar words and limit to 160 before insert the statement into table.
0
doramail05
Asked:
doramail05
  • 2
1 Solution
 
Eugene ZCommented:
please tell more , clarify

are you using or plan to use Full-text search?
what is you sql server version, edition , serv pack?
160- is number of bad words in 1 message?
what do you plan to do whe n1 or more found ? rollaback? etc
0
 
doramail05Author Commented:
got it oredi

BEGIN 
     SET NOCOUNT ON 
    -- vulgar text filter start
		set @ReturnString=5
		DECLARE @S varchar(max),@Split char(1), @X xml
		SELECT @S = @Message, @Split = ' '
		SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')
		DECLARE @vtext int
		--set @vtext=

		set @vtext=(select COUNT(*) from(
		SELECT [tValue] = T.c.value('.','varchar(20)'),vulgarword FROM @X.nodes('/root/s') T(c)
		left join tVulgarTable on T.c.value('.','varchar(20)') =tVulgarTable.vulgarword
		)
		t
		where t.vulgarword is not null)
		print @vtext
		
--Vulgar text filter end
    
     if (@vtext>0)
		begin
			set @ReturnString = 0
		end
	if (len(@Message)>160)
		begin
			set @ReturnString = 1
		end
	if (len(@Message)>160 and @vtext>0)
		begin
			set @ReturnString = 3
		end
	
	 if ((len(@Message)<160 or len(@Message)=160) and @ReturnString=5)
		begin
			
			set @ReturnString = 2
		
        
     INSERT INTO dbo.tableA

Open in new window

0
 
doramail05Author Commented:
assisted from others
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now