Find records with alternate emails

Transact-SQL
1

I have a table with a field for email distribution, and I am trying to figure out if there is a way I can select each record that contains an email outside our company, for example:

DECLARE @Records TABLE
(
ID int,
Distrib varchar(max)
)

insert into @Records values
(1, 'First@email.com;Second@email.com'),
(2, 'Third@email.com;Different@other.com'),
(3, 'Fourth@email.com;Fifth@email.com')

In this case record ID 2 is the one I would want to select as it contains an email that does not have @email.com in it.

2

Isn't it always the way, I was going to mention I am on 2014 so I cant use STRING_SPLIT, but then I realized I can do the same thing with a user defined function. If anyone has any other / better ideas, I would be totally open to that as well!

2016 and newer:
select * from @Records
cross apply STRING_SPLIT(Distrib,';')
where value not like '%@email.com'

2014 and older (after creating split function):
select * from @Records
cross apply dbo.split(Distrib,';')
where splitdata not like '%@email.com'

3

I don't know if it's a better idea, but just as exercise ...
I have to replace the text @email.com with empty string replace(Distrib,'@email.com','' )
Then search if we still have the character @ in the new field charIndex('@',replace(Distrib,'@email.com','' ))

;WITH aCTE AS
(
select * 
  ,charIndex('@',Distrib) as pos_for_@
  ,replace(Distrib,'@email.com','' ) as distrib_no_email
  ,charIndex('@',replace(Distrib,'@email.com','' )) as new_Distrib
from @Records AS R
)

select ID , Distrib 
from aCTE as a
where a.new_Distrib > 0

output :

ID Distrib
2 Third@email.com;Different@other.com
4

Oh that's a great idea, I was really over thinking this, thanks!

5 

select * from @Records where Distrib not like '%@email.com';

image

1 Like
6

That only works for this very specific instance, but if the other email came first it would not work, and even more so if there are 10-15 emails listed and it's somewhere inside:

DECLARE @Records TABLE
(
ID int,
Distrib varchar(max)
)

insert into @Records values
(1, 'First@email.com;Second@email.com'),
(2, 'Third@email.com;Different@other.com'),
(3, 'Fourth@email.com;Fifth@email.com')
(3, 'Another@other.com;Sixth@email.com')

7

Extending @jotorre_riversidedpss.org solution

select * from @Records where Distrib+';' not like '%@email.com;%';
8

moving forward what do you plan to do with your data. leave it as is or redesign and clean it up so this type of stuff no longer happens. this text parsing stuff will 100% come to bite you.

9

Unfortunately I have to leave it as is, however I am the front end developer so I can ensure clean data as they edit in the interface. I think the solution of finding and replacing @email.com with blank and checking for any @ should do the trick seeing as how I made it impossible for them to add any invalid emails to the list before saving. I guess in the end if anything fails and bad data gets in then this will just help catch it as well, fortunately there are enough controls around it that I feel pretty comfortable regardless.