Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2009
    Posts
    47

    Unanswered: How To Extract Domain Name From An Email Address??

    hello everybody,

    I need UDF for extracting domain name from email address.

    I have data like : Kim;<abc@abc.com;xyz@aa.com;mnop@abc.com>

    the output will be:

    abc.com,aa.com

    Note: skip the name if there is no domain name and skip the same domain name if there are more than 1 email address

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Look up the CHARINDEX function in Books Online. Think about how you can use that with the SUBSTRING function.

  3. #3
    Join Date
    Jan 2009
    Posts
    47
    thanks for replay,
    but problem is we don't know how many email address are there

  4. #4
    Join Date
    Jan 2009
    Posts
    47

    Red face

    anybody can seperate domain name only from email address?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rudba
    anybody can seperate domain name only from email address?
    SUBSTRING(email,CHARINDEX('@',email)+1,LEN(email)-CHARINDEX('@',email))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2009
    Posts
    47
    if i have a multiple email address, it does not work.
    e.g.
    mm@aa.com;aaa@my.com;abc@my.com;

    the output is:
    aa.com;aaa@my.com;abc@my.com;

    I want this output:
    aa.com;my.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rudba
    if i have a multiple email address, it does not work.
    that's too bad, isn't it

    i gave you the exact solution to your question in post #4

    it's up to you to employ this solution into your UDF
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2009
    Posts
    47
    yes i know, can we create own function for my requirment?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rudba
    yes i know, can we create own function for my requirment?
    seriously, you should be able to take over from here if you are a competent database developer

    alternatively, you might wish to hire someone who can do it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2009
    Posts
    47
    guys this is a forums, if anybody got the problems we wants to sharing code each othere .......its not like chatting room and its not a to get the business!!!
    Last edited by rudba; 02-27-09 at 15:12.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, a forum ~is~ like a chatting room

    and there is a difference between discussing solutions and just handing you the answer

    if we always just hand you the answer, you will never learn

    you must eventually take responsibility for producing your own solutions -- you cannot just keep coming here to demand that we supply you with answers

    and besides, i did not mean to suggest that you would hire ~me~ to create your solution

    for one thing, i would not accept the assignment

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Stop, or we'll taunt you more!!

    Dang...got here too late. Most of the good chastising has already been done.

    Good job in the translation though, Rudy! I did not have a clue about what "getting the business" meant to him until I saw your response.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as opposed to "giving him the business" (another obscure football reference)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2008
    Posts
    135
    Quote Originally Posted by rudba
    if i have a multiple email address, it does not work.
    e.g.
    mm@aa.com;aaa@my.com;abc@my.com;

    the output is:
    aa.com;aaa@my.com;abc@my.com;

    I want this output:
    aa.com;my.com
    try this once
    declare @EMAILS table(Email varchar(1000))
    INSERT INTO @EMAILS SELECT
    'mangal.pardeshi@indiamvps.net;mangal@my.com;rara@ my.com'UNION ALL SELECT
    'aa@abc.com;bcd@aaa.com;aas@abc.com;dd@aaa.com' UNION ALL SELECT
    'aaa@bbb.com;mnop@xyz;aaa@abc.com'

    SELECT
    SUBSTRING(s.Email,charindex(';',s.Email,v.number)+ 1,abs(charindex(';',s.Email,charindex(';',s.Email, v.number)+1)-1-charindex(';',s.Email,v.number)))as email
    into #temp
    FROM @EMAILS AS s
    INNER JOIN master..spt_values AS v ON v.Type = 'P'
    and v.number > 0
    and v.number <= len(s.Email)
    WHERE substring(';' + s.Email, v.number, 1) = ';'

    select stuff((select distinct
    ';'+substring(email, charindex('@',email,1),len(email))
    from #temp for xml path('')),1,1,'')

    select case when charindex('.',email,charindex('@',email,1)+1) > 0
    then substring(email, charindex('@',email,1)+1,charindex('.',email,chari ndex('@',email,1)+1) -charindex('@',email,1)-1)
    else substring(email, charindex('@',email,1)+1,len(email)) end as domain
    from #temp

    drop table #temp

Posting Permissions

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