CODEWITHSUNDEEP
sqlsql-serverregexparsingsql-like
Pythonn00b
Pythonn00b

Reputation: 325

MS-SQL List of email addresses LIKE statement/regex

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 [email protected] 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: 

[email protected], [email protected], [email protected]     1
[email protected], [email protected]                             0
[email protected]                                             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.

Upvotes: 1

Views: 608

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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.

Upvotes: 2

AdamL
AdamL

Reputation: 13141

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 '[email protected], [email protected], [email protected]' as field union all 
    select '[email protected], [email protected]' union all 
    select '[email protected]' 
) x

Upvotes: 3

Related Questions