Skip to Content
author's profile photo Former Member
0
Former Member

Contacts by Email address search

Posted on Jan 25, 2005 at 10:30 AM 211 Views

Hi All,

I want to be able to find all the contacts by using a wildcard search on eamil addresses.

When I look at ADR6, the field SMTP_ADDR is 241 long but is case sensitive.

There is a second field called SMTP_SEARCH which is upper case but is only 20 long.

At the moment I can either search using the first option but, obviously, I have the matching case issue. Or I can search on the first 20 characters on the email address, which is a bit restictive.

For example. Andy.Nother@BigCompany.co.uk will have ANDY.NOTHER@BIGCOMPA in the search field. If I search for bigcompany this will not be found using either field because the case doesn't match and it the pattern doesn't exist in the search field.

Has anyone done this and found a way around it ???

Cherers

James

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 10:39 AM
    0

    Have you tried a SELECT statement yet ?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Andreas Mann
    Andreas Mann
    Posted on Jan 25, 2005 at 10:43 AM
    0

    Hi James ,

    i'd create my own search help

    to the field adr6-SMTP_ADDR

    -> http://10.243.51.62/helpdata/EN/cf/21ee2b446011d189700000e8322d00/frameset.htm

    regards Andreas

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 11:04 AM
    0

    Hi James,

    It is not clear from your description whether you want to code a SELECT statement in your program or whether your are talking about the SE16 transaction (Data Browser).

    If it is SE16 that you are referring to, then I'm able to do a case-sensitive search for this field.

    If it is a SELECT statement, then give the one that you are using.

    All the best...

    Regards,

    Aand Mandalika.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 11:26 AM
    0

    I am using a select statement.

    Basically it's

    Data : w_smtp_addr type AD_SMTPADR.

w_smtp_addr = '%bigcompany%'.

  select *
         into table my_tab
         from adr6
         where smtp_addr like w_smtp_addr.

  check : sy-subrc eq 0.

  translate w_smtp_addr to upper case.

  select *
         into table my_tab
         from adr6
         where smtp_srch like w_smtp_addr.

    As I say, both searches return no data because of the way the fields are set up.

    Cheers

    James

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Jan 25, 2005 at 12:50 PM

      Hello Subramanian,

      I did not get what you meant. What is the need to use Native SQL when it can be achieved through Open SQL ?

      Moreover, the data in question is stored in the database in the case-sensitive format. So there should not be any problem retrieving it in the same format.

      Regards,

      Anand Mandalika.

    Comment on This Answer
  • author's profile photo Andreas Mann
    Andreas Mann
    Posted on Jan 25, 2005 at 02:57 PM
    0

    Hi JamesMacdonald,

    you can't find JamesMacdonald

    with search string %jamesmacdonald% or its

    translation in upper case %JAMESMACDONALD%

    -> you firstly must translate mail_adr from db

    to upper case ( in internal table )

    2. translate search string to upper case

    3. read table itab ...with search string

    regards Andreas

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 03:08 PM
    0

    I think you are all missing the problem.

    Create an email address exaclty like this;

    myFirstName.mySurName@ThisIsMyCompany.Com

    This email address is 41 characters long. It is important you enter it in the same case as above.

    Now try to search for mycompany.com. using SQL.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Jan 25, 2005 at 03:57 PM

      Hi James,

      I did notice that. I would not be able to test it , until day after tomorrow. Perhaps I should have explained it in more detail.

      Look in this message contacts-by-email-address-search

      for the code.

      Here I have used <b>LOWER(ADR_NAME)</b> on the left hand side of the equal to('=') sign, therefore all the results obtained from the database will be first converted to lowercase and then compared with your string. I do not think this will work in Open SQL, so you have to try native SQL.

      Let us know if that worked. I am crossing my fingers.

      Regards,

      Subramanian V.

      P.S. - Next time I shall make sure I will test the code and send it to you

  • author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 05:00 PM
    0

    Hi Subramanian,

    Sorry, I didn't spot your solution last time.

    I think this may work, the only issue would performance. I need to search from around 750,000 records !

    I have tried to understand why the user would want to search on the middle part of an email address. As yet they haven't given me a good reason. The search help this is part of allows them a multitude of other parameters to search on.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.