Solved

sql query question

Posted on 2006-10-24
11
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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