0

Hopefully an easy problem for an experienced SQL person. I have an application which uses SQL Server, and I cannot perform this query in the application, so I'm hoping to back-door it, but I need help.

I have a table with a large list of emails and all its metadata. I'm trying to find email that is only between parties of this one company and flag them.

What I did was search where companyName.com is in To and From and marked a TagField as 1 (I did this through my application's front end).

Now what I need to do is search where any other possible values, ignoring companyName.com exist in To and From where I've already flagged them as 1 in TagField. From will usually just have one value, but To could have multiple, all formatted differently, but all separated by a semi-colon (I will probably have to apply this same search to CC and BCC columns, too).

Any thoughts?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jim G
  • 51
  • 6

2 Answers2

0

Replace the ; with the empty string. Then check to see if the length changed. If there's one email address, there shouldn't be a ';'. You could also use the same technique to replace the company name with the empty string. Anything left would be the other companies.

select email_id, to_email
from yourtable
where TagField = 1 and len(to_email) <> len(replace(to_email,';',''))

This solution is based on the following thread

Number of times a particular character appears in a string

LAS
  • 829
  • 5
  • 7
0

So I went an entirely different route and exported my data to a CSV and used Python to get to where I needed. Here's the code I used in case anybody needs it. What this returned for me was a list of DocIDs (unique identifiers that were in the CSV) where ever there was an email address in the To field that wasn't from one specific domain. I went into the original CSV and made sure all instances of this domain name were in all lowercase, too.

    import csv
    import tkinter as tk
    from tkinter import filedialog

    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.askopenfilename()
    sub = "domainname"

    def findMultipleTo(dict):
        for row in reader:
            if row['To'].find(";") != -1:
                toArray = row['To'].split(';')
                newTo = [s for s in toArray if sub not in s]
                row['To'] = newTo
            else:
                row['To'] = 'empty'

            with open('location\\newCSV-BCCFieldSearch.csv', 'a') as f:
                if row['To'] != "empty" and row['To'] != []:
                    print(row['DocID'], row['To'], file = f)
                else:
                    pass


    with open(file_path) as csvfile:
        reader = csv.DictReader(csvfile)    

        findMultipleTo(reader)
Jim G
  • 51
  • 6