-- solution

;WITH SampleData AS (

SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL

SELECT 'No email address in this string'

)

SELECT test, x3.p3

FROM SampleData

CROSS APPLY (SELECT p1 = NULLIF(CHARINDEX('@',test,1),0)) x1

CROSS APPLY (SELECT p2 = SUBSTRING(test,(2+p1-CHARINDEX(' ',REVERSE(LEFT(test, x1.p1)))),8000)) x2

CROSS APPLY (SELECT p3 = LEFT(p2,CHARINDEX(' ',p2,1)-1)) x3;

-- workings

;WITH SampleData AS (

SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL

SELECT 'No email address in this string'

)

SELECT *

FROM SampleData

CROSS APPLY (SELECT p1 = NULLIF(CHARINDEX('@',test,1),0)) x1

CROSS APPLY (SELECT p2 = LEFT(test, x1.p1)) x2

CROSS APPLY (SELECT p3 = REVERSE(p2)) x3

CROSS APPLY (SELECT p4 = CHARINDEX(' ',p3)) x4

CROSS APPLY (SELECT p5 = 2+p1-p4) x5

CROSS APPLY (SELECT p6 = SUBSTRING(test,p5,8000)) x6

CROSS APPLY (SELECT p7 = LEFT(p6,CHARINDEX(' ',p6,1)-1)) x7;