Remove all email id with a specific domain except one id.

Question

0.00/5 (No votes)

See more:

Hi All,

I'm working in an ecommerce company. I need to take a report which does not contain the orders placed from company employees office email id except one. For eg: suppose my office id is 'XXXX@mycmpny.com'. I have to exclude all records with _@mycmpny.com except YYYY@mycmpny.com. How can I do this in sql server? When I use 'not like' function, it is excluding all. Please help.

Thanks in advance.

Regards,

Shanif

Posted 23-Apr-13 23:12pm

ShanifHassan

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ShanifHassan · Answer 1 · 2013-04-24T01:34:00

SQL

WHERE   CAST(CONVERT(VARCHAR,trndate,101)AS DATETIME) BETWEEN @DteFrmDate AND @DteToDate AND ex.ExcVarDomain IN (SELECT * FROM dbo.UdfEsSplitPrdCls(@VarEstoreList,'|'))
            AND TrnVarFrmEid  not like '%_@natl._%'
and TrnVarFrmEid not like '%_@chennaibazaar._%'
and TrnVarFrmEid not like '%_@diy._%'
and TrnVarFrmEid not like '%_@cbazaar._%'


In this we need to check email id should  contain chat@cbazaar.com. but we given the condition 'and TrnVarFrmEid not like '%_@cbazaar._%''. Obviously it will not shown there. so kindly rearrange this statement to get chat@cbazaar.com and not other @cbazaar.com

gvprabu · Answer 2 · 2013-04-23T23:47:00

Solution 1

Hi,

Tel me the delimiter for Differenciate each email IDs. then u can use function and Seperate all Email IDs and put it in Tem Table or Table Type veriable then u can process what ever u want.
In Following my Post... check the Answer then u ll get Clear Idea.

Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]

If any Clarificatiosn Get back to me.... :-)
Regards,
GVPrabu

Posted 23-Apr-13 23:47pm

gvprabu

Comments

ShanifHassan 24-Apr-13 6:55am

Hi Prabhu,

Thanks for quick response.
My report has columns like name,addresscolumn,order no,CustomerEmails etc. My testing team will be placing test orders. Right now all test orders orders come in this report. So I need to avoid those test data. I have a option that to check a condition: where CustomerEmails not like ('_@mycmpny.com'). But 1 email id YYYY@mycmpny.com has to come in the report. At this point I got stuck. How to include this particular email and exclude all other emails with same domain.

gvprabu 24-Apr-13 7:01am

See all Emai IDs in Single column Right So, U can't use LIKE or other Comparition Operation With out Split Process. See If u Use Split Function You will get the Details in ROW Wise then U can do all Include or Exclude Process. IF any difficulties then Send the Table and Sample Data. I will help you.