Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql query question

Posted on 2006-10-24
11
Medium Priority
?
449 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 1000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Industry Leaders: 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
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.

618 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