SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'
Anything more complex will likely return false negatives and run slower.
Validating e-mail addresses in code is virtually impossible.
EDIT: Related questions
- I’ve answered a similar question some time ago: TSQL Email Validation (without regex)
- T-SQL: checking for email format
- Regexp recognition of email address hard?
- many other Stack Overflow questions