Prev: SSIS - sql server integrated services - like OLAP?
Next: xml dml conditional attribute inserting does not work
From: Eric Russell on 1 Jul 2008 13:25
One approach is to strip out the email server name you don't need
(@google.com), and then see if any additional server names are left. This is
just a simple WHERE clause and requires no defined functions or accessory
tables.

declare @T table (sentto varchar(8000))

insert @T (sentto) values ('Doe, John [john.doe(a)google.com]; Dom, Jane
[jane.dom(a)yahoo.com];')
insert @T (sentto) values ('Doe, John [john.doe(a)google.com];')
insert @T (sentto) values ('Doe, John [john.doe(a)googlemart.com];')
insert @T (sentto) values ('Dom, Jane [jane.dom(a)yahoo.com];')

select sentto
from @T
where replace(sentto,'@google.com','') like '%@%'

------------------------------------
Doe, John [john.doe(a)google.com]; Dom, Jane [jane.dom(a)yahoo.com];
Doe, John [john.doe(a)googlemart.com];
Dom, Jane [jane.dom(a)yahoo.com];


"TG" wrote:

> 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 18:58
Eric,

I tried ur approach by using the following:


select fileid as [id],sentto
into temp_domain
from emails
where sentto like '%@google%'


select * from temp_domain



declare @T table (temp_domain varchar(8000))


select sentto
from @T
where replace(sentto,'...@google.com','') like '%@%'


select * from temp_domain



but I don't see any difference. I see all the records still there.

What is wrong?

Thanks a lot!

Tammy


 | 
Pages: 1
Prev: SSIS - sql server integrated services - like OLAP?
Next: xml dml conditional attribute inserting does not work