• ChrisM@Work (6/25/2012)


    This works quite nicely too:

    SELECT

    EmployeeID,

    Email,

    DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),

    DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)

    FROM @t

    CROSS APPLY (

    SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),

    PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)

    ) x

    CROSS APPLY (

    SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),

    PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)

    ) y

    Absolutely awesome, Chris. "Cascading Cross Applys" (cCA for short! :-D). Looks like it might beat the tar out of cascading CTE's of a similar nature. To be honest, I didn't know such a thing was possible because I didn't even consider that one CA might be able to use the output of another. This crazy Monday has turned into something good after all!

    You should write a "Spackle" article on it, Chris.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions