Solved

sql query question

Posted on 2006-10-24
11
437 Views
Last Modified: 2012-06-21
I'm using an application called GFI Mail Archiver.  All emails coming in and out of our network are dumped to a SQL database that is searchable via a .NET website.  When first implementing this it archived thousands of undesired emails.  I would like to perform a query to find these emails and delete them.  Below are a series of commands I found that will do the trick.  What will the 'where full_subject like' command find?  Can I use a wildcard with this command like:

where full_subject like 'Bayesian' (any email with Bayesian in the subject will be found in the query?  Below is the series of commands...

declare @tbl table
(id int)
-- filter messages to be deleted by subject
insert into @tbl
select id from arc
where full_subject like 'j.bloggs@domian.com - Bayesian filter'
--
delete from arc_sources where id in (select id from @tbl)
delete from arc_bodies where id in (select id from @tbl)
delete from arc_att where id in (select id from @tbl)
delete from arc_add where id in (select id from @tbl)
delete from arc_owners where id in (select id from @tbl)
delete from arc where id in (select id from @tbl)
delete from arc_threads
where NOT EXISTS(select id from arc where thread_id=arc_threads.thread_id)

0
Comment
Question by:gopher_49
  • 6
  • 5
11 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 17800020
where full_subject like '%Bayesian%'
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 250 total points
ID: 17800022
might be slightly more efficient to use charindex instead of like:


where charindex('Bayesian', full_subject) > 0
0
 

Author Comment

by:gopher_49
ID: 17800029
when using where charindex('Bayesian', full_subject) > 0 - any subject with 'Bayesian' listed somewhere in the subject will be deleted?  If so, this will work just fine.
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17800032
If 'Bayesian Filter' is always the last 2 words in the full subject field then use this:

where right(full_subject,15) = 'Bayesian filter'
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17800039
Yes, these two statements are functionally identical.  I prefer charindex because i am not a huge fan of Like

where FieldName like '%TEXT%'

where charindex('TEXT',FieldName) > 0
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:gopher_49
ID: 17800040
I'll try it this evening.  I'll get back with you in the morning.

thanks.
0
 

Author Comment

by:gopher_49
ID: 17800963
The keywords I'll be using will fall in different parts of the subject line.  Below is an example of a subject that will have the word 'Bayesian' in it.  Will the where charindex('TEXT',FieldName) > 0 work?

user@domain.com - Bayesian Filter detected spam - Match the Whole Family in Cozy Sleepwear, Plus 15% Off
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17803727
yes
0
 

Author Comment

by:gopher_49
ID: 17806751
great.  I plan to to delete  a TON of emails based on this.  I would say around 200,000 or so.  I'll let you know if and when my server fries!  I'll get back with you first thing in the morning.

thanks.
0
 

Author Comment

by:gopher_49
ID: 17806968
I have one more question.  What if the subject has the phrase 'sending mail server'.  And I'm using 'sending mail server' in the where charindex command.  Will it require the subject to have those words in that order?  That is what I would prefer to void deleting emails that shouldn't be deleted.  The only emails that will have 'sending mail server' in the subject are emails that have been marked as spam and I would like to delete them from the db.
0
 

Author Comment

by:gopher_49
ID: 17809027
the charindex('Bayesian', full_subject) > 0 command worked incredibly well in conjunction with the syntax mentioned above.  I cleared up over 250,000 spam emails.  I now have exclusions enabled to void archiving emails that are marked as spam.  The above syntax got rid of the junk that was entered into the archive before me getting a chance to enable the exclusion.  Anyway, it worked perfect.

thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Ranking Based On Value 3 29
SQL JOIN 6 32
while loop in html mail format 5 32
Test a query 23 15
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now