See if something like this will get you started. Please notice how I posted ddl and sample data in a consumable format.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

Ticket int,

contacts varchar(100)

)

insert #Something

select 4368642, 'Customer Support, Customer45@hotmail.com' union all

select 4368640, 's_prod_i3admin, s_prod_i3admin, Customer12@yahoo.com' union all

select 4368023, '1234Customer@msn.com'

select *

from #Something s

cross apply dbo.DelimitedSplit8K(s.contacts, ',') x

where CHARINDEX('@', x.Item) > 0

You can find the code for the DelimitedSplit8K function by following the link in my signature about splitting strings.