Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: email address domain strip of query

    Could this query be made more efficient?
    It takes ages to run it. It strips of everything from an email address and keeps only the domain.

    SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, 100)) - 1) AS mail_domain

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    got 2005? string manipulation is faster in CLR.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    unfortunately no i don't. I may upgrade soon but for now it is 2000 im using.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I assume that if you take this column out of the select list, the plan remains the same, but the query time goes up? Does the query time remain the same when you add the raw email address to the select list (and comment out all the substringing)? I am wondering, if you accidentally got a covering index on all of the fields not associated with the email addresses, or maybe the query is using different indexes for the query with all the substrings.

    Also, not to be a nit picker, but which part of the domain are you after? An email address can be very complex. My brother has an email address that ends in @haystack.mit.edu. Some of our British posters would have addresses like @company.co.uk. I am guessing you would be interested in the "mit" or the "company", but not in "co' or "haystack".

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    yes its from select list and no joins so it should be very fast. I se now that i have to make this to work with thoose split domain names aswell... damn

    My question about the query was more kinda of " is this a stupid way to do this"?
    i can live with the time it takes to run this query it is after al quite a large table.

    thx for your help

Posting Permissions

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