Solved

sql query question

Posted on 2006-10-24
11
442 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

734 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