Solved

sql query question

Posted on 2006-10-24
11
439 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 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

10 Experts available now in Live!

Get 1:1 Help Now