Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Getting part of an e-mail address

    Hi

    I have various e-mail addresses in my db as strings, but would like to only get the part of the e-mail address before the @ symbol.

    Is this possible, baring in mind that the length of the text before the @ symbol varies in length?

    Thanks
    Last edited by KevCB226; 02-28-06 at 07:18.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on 'when' you wanted to perform your extraction of the desired part of the email address

    you could look at the VBA string functions - split() is worth a look in particular, you could consider instr(), left$() etc...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Two words: InStr function ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree instr will do the job, but split may be more appropriate in this case

    Code:
    Public Function GetEmailPrefix(strEMail As String) As String
    if isnull(strEmail) then exit function
    Dim strEMailElements() As String
    strEMailElements = Split(strEMail, "@")
    GetEmailPrefix = strEMailElements(0)
    End Function
    It may not be the most efficient code but if placed in a public code module and called in the sql query it will return the relevant parameter. You could remove some of the
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Mark,

    I would say no to Split ... Why? With InStr, you can return just the address part from within the SQL ... Ex:

    SELECT Left(MyEmailAddress,Len(MyEmailAddress)-InStr(MyEmailAddress,'@')) FROM ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    Kev,

    Have a look at the attached sample db, this should work for you.


    M Owen,
    I tried yours but it gave me me this;

    johna@myisp.
    from this:
    johna@myisp.com.au
    Attached Files Attached Files
    Regards,



    John A

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by M Owen
    Mark,

    I would say no to Split ... Why? With InStr, you can return just the address part from within the SQL ... Ex:

    SELECT Left(MyEmailAddress,Len(MyEmailAddress)-InStr(MyEmailAddress,'@')) FROM ...
    the main reason I'd go with a function using split is that personally I would expect a user requirement in the very near future saying in effect - its nice to get the person stipped off the email, can we strip off the compay, or even top level identifier.

    if its in a function then the function can be extended or modified by passing a parameter (possibly optional so it doesn't uspet existing code).

    Dunno maybe its just that the users I work with are fully paid up members of the awkward squad. But this sort of thing happens all to often here
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by healdem
    the main reason I'd go with a function using split is that personally I would expect a user requirement in the very near future saying in effect - its nice to get the person stipped off the email, can we strip off the compay, or even top level identifier.

    if its in a function then the function can be extended or modified by passing a parameter (possibly optional so it doesn't uspet existing code).

    Dunno maybe its just that the users I work with are fully paid up members of the awkward squad. But this sort of thing happens all to often here
    You do have a point there Mark ...


    CORRECTION:

    SELECT Left(MyEmailAddress,InStr(MyEmailAddress,'@')-1) FROM ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    the main reason I'd go with a function using split is that personally I would expect a user requirement in the very near future saying in effect - its nice to get the person stipped off the email, can we strip off the compay, or even top level identifier.

    if its in a function then the function can be extended or modified by passing a parameter (possibly optional so it doesn't uspet existing code).

    Dunno maybe its just that the users I work with are fully paid up members of the awkward squad. But this sort of thing happens all to often here
    That's why I store my email addresses in addressee and domain feilds (with a 1:m relationship for my domains)

    pootle "smug smarty pants" flump
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •