Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with extracting a part of the string

    Hi everybody

    couldn't figure out how extract both parts of email address ....
    example email

    firstpart@secondpart.org

    I need to extract the "firstpart" from email address and the "secondpart.org" as second part.. I'm trying the left() function but since emails have no fixed length I couldn't use that... is there a possibility I could do that with of the course the division would be up to the @ sign...

    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    use charindex and substring.
    “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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use CHARINDEX('@',emailaddress) to determine the position of the @

    this returns 0 if it isn't found, otherwise a positive integer

    use this inside the two SUBSTRING functions that pull out the first part and the last part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aargh, sorry, thrasymachus, i was typin while you were answerin
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    DECLARE
    Code:
     
    @EmailName nvarchar(200)
    ,@Alias nvarchar(100)
    ,@DomainName nvarchar(100)
    SELECT 
    @EmailName ='firstpart@secondpart.org'
    SELECTcharindex('@',@EmailName)
    SELECT @Alias =left(@EmailName,charindex('@',@EmailName)-1)
    SELECT @DomainName =right(@EmailName,len(@EmailName)-charindex('@',@EmailName))


    Have not tested .. so use at your own risk ..

    Plus you need to test for bounday conditions
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Aug 2006
    Posts
    87
    thanks for all your suggestions tried it and came up with this which works fine



    select email
    ,Left(email, CharIndex('@', email+'@')-1 ) AS leftside
    ,Stuff(email, 1, CharIndex('@', email+'@'), '') AS domainside
    FROM table

    again thanks

Posting Permissions

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