SQL Server : SQL Script to Validate Email Address (without regex)

Here i am discussing about, Validate Email Address. There is few simple pattern, i have tried with as per my requirement. Hope this work for you-

Scenario 1:

SELECT * FROM MstUser WHERE EmailAddress NOT LIKE '%_@__%.__%'

Scenario 2:

SELECT * FROM MstUser WHERE EmailAddress NOT LIKE '_%@_%.__%'

Scenario 3:

                    We can use above pattern to Validate Email Address using Function as below:

CREATE FUNCTION [dbo].[fn_ValidateEmail] (@InputValue Varchar(200))
RETURNS Bit
AS
BEGIN
     RETURN CASE
        WHEN @InputValue LIKE '%_@__%.__%' THEN 1
        ELSE 0
     END
END

We call above function like below:

SELECT [dbo].[fn_ValidateEmail] (EmailAddress) FROM [dbo].[MstUser]

If we want simply, only once you want to Validate Email Address using "Case Statement" as below:

SELECT (CASE WHEN EmailAddress LIKE '%_@__%.__%' THEN 1 ELSE 0 END) FROM [dbo].[MstUser]

By using above query, you can just use it without needing to call a function.


Above queries tried and tested by me.