help with query (does string exist in string)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query (does string exist in string)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-20 : 14:12:05

Hi,

I have an INSERT query that I am looking to modify.

Right now its quite simple as seen below.

INSERT INTO [dbo].[tblMessage]
([MessageFrom],
[MessageTo],
[Message],
[Subject],
[date],
[IP],
[Checked],
[deletedBySender],
[deletedByRecipient]
)

VALUES
(@MessageFrom,
@MessageTo,
@Message,
@Subject,
@date,
@IP,
0,
0,
0)

I am trying to figure out the best way to do the following.

I have added a table called tblSpam_Keywords as seen below as well.

Basically when the SPROC is fired, I want to take the @message string that is passed to the parameter, and run it against the query that selects all the "banned words".

If a banned word is found, we will proceed with the INSERT of the message, however we will also do another insert into a reporting table logging the violation. We will insert the SELECT SCOPE_IDENTITY() into the reporting table.

Any opinions on whether this is best handled in the web app, or contained in a SPROC ?

Im sure SQL 2005 has great built in caching, I was thinking of doing it in the web app because the Spamkeywords list is pretty static, and I think I can do it fairly efficiently with a cached datatable in my .NET code. Now I am thinking its probably better to do in the SPROC.

How can I determine if the passed string contains a word from the tblSpam_Keywords table ??

Any help much appreciated.

Thanks once again!!
mike123

CREATE TABLE [dbo].[tblSpam_Keywords](
[spamWordID] [int] IDENTITY(1,1) NOT NULL,
[keyword] [varchar](250) NOT NULL,
[typeID] [tinyint] NOT NULL
) ON [PRIMARY]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 15:07:06

Here is one way to detect a "spam" word in sql:


use tempdb
go
CREATE TABLE [dbo].[tblSpam_Keywords](
[spamWordID] [int] IDENTITY(1,1) NOT NULL,
[keyword] [varchar](250) NOT NULL,
[typeID] [tinyint] NOT NULL
)

insert tblSpam_keywords (keyword, typeid)
select 'viagra', 1 union all
select 'Nigeria', 1 union all
select 'enlargement', 1 

go

declare @message varchar(50)
set @message = 'This is a clean message'
set @message = 'Time to renew your viagra prescription'

if exists (select 1 from tblSpam_Keywords where charindex(keyword, @message) > 0)
begin
       print '@message contains spam'
end
else
begin
       print '@message does not contains spam'
end

go
drop TABLE [dbo].[tblSpam_Keywords]

Be One with the Optimizer
TG

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-20 : 15:46:56

Hi TG,

Thanks very much. How efficient do you think this is ?

I am trying to weigh doing this logic in the web application (comparing the message before its inserted into the DB to a string thats stored in the application variable )

vs

if exists (select 1 from tblSpam_Keywords where charindex(keyword, @message) > 0)

I realize its tough to say without actually performance testing, but I'm looking for any opinions available :)

I am just worried about hitting tblSpam_Keywords all the time. It wont be that big of a table, and wont be modified often. Starts with 0 records and adds 10 per day approx.

I assume it will be cached in SQL server as efficiently as caching it manually in ASP.NET code ? Is that a fair guess?

Thanks very much,
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources