how to strip specific email address from nvarchar field with multiple addresses
in [Programming SQL]
|
From: TG on 30 Jun 2008 23:16 Hi! I have the following ddl in sql server 2005 id int sentto nvarchar(1000) sentto can be like this: Doe, John [john.doe(a)google.com]; Dom, Jane [jane.dom(a)yahoo.com]; Ross, Julia [julia.ross(a)google.com] or it can be only one email address. I need to only keep all those records that do not have @google.com in them, but it there is a record like the example above with google inside, I still need to keep in that case. How can I achieve that? Thanks a lot! Tammy
From: Uri Dimant on 1 Jul 2008 01:04 TG Untested , but could you use NOT LIKE '%@google.com %'? "TG" <jtammyg(a)yahoo.com> wrote in message news:bd7c5558-690c-40ca-8b0e-e052bcf8eefe(a)d1g2000hsg.googlegroups.com... > Hi! > > I have the following ddl in sql server 2005 > > id int > sentto nvarchar(1000) > > > sentto can be like this: > > Doe, John [john.doe(a)google.com]; Dom, Jane [jane.dom(a)yahoo.com]; Ross, > Julia [julia.ross(a)google.com] > > > or it can be only one email address. > > I need to only keep all those records that do not have @google.com in > them, but it there is a record like the example above with google > inside, I still need to keep in that case. > > How can I achieve that? > > Thanks a lot! > > Tammy
From: Rahul on 1 Jul 2008 01:52 On Jul 1, 8:16 am, TG <jtam...(a)yahoo.com> wrote: > Hi! > > I have the following ddl in sql server 2005 > > id int > sentto nvarchar(1000) > > sentto can be like this: > > Doe, John [john....(a)google.com]; Dom, Jane [jane....(a)yahoo.com]; Ross, > Julia [julia.r...(a)google.com] > > or it can be only one email address. > > I need to only keep all those records that do not have @google.com in > them, but it there is a record like the example above with google > inside, I still need to keep in that case. > > How can I achieve that? > > Thanks a lot! > > Tammy Try this, Create Table Email ( id int, sentto nvarchar(1000) ) Insert Into Email Select 1, 'Doe, John [john....(a)google.com]; Dom, Jane [jane....(a)yahoo.com]; Ross, Julia [julia.r...(a)google.com]' Union Select 2, 'Dom, Jane [jane....(a)yahoo.com]; Ross, Julia [julia.r...(a)yahoo.com]' Select * From Email Where sentto Not Like '%google.com%' Create Function dbo.BreakEmail ( @Emails nVarChar(1000) ) Returns @EmailAddress Table ( Email nVarChar(1000) ) Begin Select @Emails = @Emails + ';' While PatIndex('%;%', @Emails) > 0 Begin Insert Into @EmailAddress(Email) Select SubString(@Emails, 1,PatIndex('%;%', @Emails) -1) Select @Emails = SubString(@Emails, PatIndex('%;%', @Emails) +1, Len(@Emails)) End Return End Select E.Id, BE.Email From Email E Cross Apply dbo.BreakEmail(E.Sentto) BE Rahul
From: Dan Guzman on 1 Jul 2008 07:30 > sentto can be like this: > > Doe, John [john.doe(a)google.com]; Dom, Jane [jane.dom(a)yahoo.com]; Ross, > Julia [julia.ross(a)google.com] IMHO, the best approach is to store multiple addresses in a separate table rather than in a single column. The single column method violates normalized database design rules and is why this task is more difficult than it needs to be. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "TG" <jtammyg(a)yahoo.com> wrote in message news:bd7c5558-690c-40ca-8b0e-e052bcf8eefe(a)d1g2000hsg.googlegroups.com... > Hi! > > I have the following ddl in sql server 2005 > > id int > sentto nvarchar(1000) > > > sentto can be like this: > > Doe, John [john.doe(a)google.com]; Dom, Jane [jane.dom(a)yahoo.com]; Ross, > Julia [julia.ross(a)google.com] > > > or it can be only one email address. > > I need to only keep all those records that do not have @google.com in > them, but it there is a record like the example above with google > inside, I still need to keep in that case. > > How can I achieve that? > > Thanks a lot! > > Tammy
From: TG on 1 Jul 2008 09:46
Uri and Rahul, The not like '%google.com%' doesn't work for me in this case, otherwise I woul have used it, since if the google.com is among other emails addresses in that particular record, i need to keep the record. Dan, They get stored like that by our e-discovery program. When you send an email to multiple people on the sent to, this application stores it in the sentto field as it was used. Do you understand? Thanks! Tammy |