Instead of write-only code, I've always found it immensely helpful to split up complex string manipulations for readability into individual parts using cascading CROSS APPLYs like this:

;WITH Notes (Note) AS (

SELECT 'aaa abc@wondwerks.com bbb'

UNION ALL SELECT 'dwain.c@sqlservercentral.com'

UNION ALL SELECT 'dwain.c@'

UNION ALL SELECT '@sqlservercentral.com'

UNION ALL SELECT 'aaa @sqlservercentral.com'

UNION ALL SELECT '@')

SELECT email

FROM Notes

CROSS APPLY (SELECT CHARINDEX('@', Note)) a (PosOfAt)

CROSS APPLY (

SELECT SUBSTRING(Note, 1, PosOfAt - 1)

,SUBSTRING(Note, PosOfAt + 1, LEN(Note))) b(Leading, Trailing)

CROSS APPLY (

SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +

LEFT(Trailing, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)

WHERE LEFT(email, 1) <> '@' AND RIGHT(email, 1) <> '@'

If you can apply this to your Notes field, you should be able to find only email addresses that contain text on both sides of the @.

Alternatively, you can also use a PATINDEX to isolate only the valid email addresses like this:

;WITH Notes (Note) AS (

SELECT 'aaa abc@wondwerks.com bbb'

UNION ALL SELECT 'dwain.c@sqlservercentral.com'

UNION ALL SELECT 'dwain.c@'

UNION ALL SELECT '@sqlservercentral.com'

UNION ALL SELECT 'aaa @sqlservercentral.com'

UNION ALL SELECT '@')

SELECT Note

FROM Notes

WHERE Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'

You'll probably need to add in a few characters to the left [0-9A-Za-z.], like underscore (_), etc. to include all possible valid email address characters.