From sql 2008 onwards patindex removes repetitive looksups

from : https://www.codelabs365.com/sql-cookbook/sql-server/tsql-validate-and-check-email-address-using-like-or-patindex-function/

CREATE FUNCTION ValidateEmail (@Email VARCHAR(100))
RETURNS VARCHAR(3)
AS
BEGIN
    RETURN (
        SELECT CASE
            WHEN ISNULL(@Email, '') = '' THEN 'No'
            WHEN @Email LIKE '% %' THEN 'No'
            WHEN PATINDEX('%["(),:;<>]%', @Email) <> 0 THEN 'No'
            WHEN SUBSTRING(@Email, CHARINDEX('@', @Email), LEN(@Email)) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 'No'
            WHEN (LEFT(@Email, 1) LIKE ('[-_.+]') OR RIGHT(@Email, 1) LIKE ('[-_.+]')) THEN 'No'
            WHEN (@Email LIKE '%[%' OR @Email LIKE '%]%') THEN 'No'
            WHEN @Email LIKE '%@%@%' OR @Email LIKE '%@%..%' THEN 'No'
            WHEN PATINDEX('_%@_%.__%', @Email) = 0 THEN 'No'
            ELSE 'Yes'
        END
    )
END;

This also validates that email starts with a character, which many others miss.

Although personal i'd use trim(@Email) = '' THEN 'No' rather than @Email LIKE '% %' THEN 'No'