MS-SQL List of email addresses LIKE statement/regex

Question

I have a column in my table called TO which is a comma separated list of email addresses. (1-n)

I am not concerned with a row if it ONLY contains addresses to Whatever@mycompany.com and want to flag that as 0. However, if a row contains a NON mycompany address (even if there are mycompany addresses present) I'd like to flag it as 1. Is this possible using one LIKE statement?

I've tried;

AND
    [To]  like '%@%[^m][^y][^c][^o][^m][^p][^a][^n][^y]%.%'

The ideal output will be:

alice@mycompany.com, bob@mycompany.com, malory@yourcompany.com     1
alice@mycompany.com, bob@mycompany.com                             0
malory@yourcompany.com                                             1

Would it be better to write some kind of parsing function to split out addresses into a table if this isnt possible? I don't have an exhaustive list of other domains in the data.

Why don't you have a table that that maps email addresses to the current table? — Explosion Pills, Mar 20 '13 at 13:13

score 3 · Accepted Answer · answered Mar 20 '13 at 13:25

It's ugly but it works. Case statement compares number of occurences of @ symbol with number of occurences of @mycompany.com (XXX.. is just for keeping the length of the string):

select 
    * 
    , flag = case when len(field) - len(replace(replace(field,'@mycompany.com','XXXXXXXXXXXXXX'),'@','')) > 0 then 1 else 0 end 
from (
    select 'alice@mycompany.com, bob@mycompany.com, malory@yourcompany.com' as field union all 
    select 'alice@mycompany.com, bob@mycompany.com' union all 
    select 'malory@yourcompany.com' 
) x

Gosh thats a good idea! I should have thought of that. Looking at these tables for too long :) — Pythonn00b, Mar 20 '13 at 13:34

score 2 · Answer 2 · answered Mar 20 '13 at 13:27

I would suggest a simple counting approach. Count the number of times that "@mycompany" appears and count the number of commas. If these differ, then you have an issue:

 select emails,
        (case when len(emails) - len(replace(emails, ',', '')) = 
                   len(emails) - len(replace(emails, '@mycompany.com', 'mycompany.com'))
              then 0
              else 1
         end) as HasNonCompanyEmail
 from t

To simplify the arithmetic, I replace "@mycompany.com" with "mycompany.com". This removes exactly one character.

MS-SQL List of email addresses LIKE statement/regex

2 Answers2