Here is the Function

CREATE FUNCTION dbo.IsValidEmail (@Email NVARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @Result BIT = 0;

-- Basic pattern check
IF @Email LIKE '%_@__%.__%' 
   AND @Email NOT LIKE '%[^a-zA-Z0-9@._-]%'
   AND CHARINDEX('..', @Email) = 0
   AND CHARINDEX('@.', @Email) = 0
   AND CHARINDEX('.@', @Email) = 0
   AND CHARINDEX(' ', @Email) = 0
BEGIN
    SET @Result = 1;
END

RETURN @Result;
END;
GO

Result

select dbo.IsValidEmail('[email protected]') --1
select dbo.IsValidEmail('[email protected]') --1
select dbo.IsValidEmail('test [email protected]') --0
select dbo.IsValidEmail('�@gmail.com') --0