Function to Check Mail in SQL Server

Hello everyone,

In this article, I will try to give information about the function that checks mail in SQL Server.

In some cases, you may want to check the e-mail address you have in SQL Server.

You can easily do this using the function below.

CREATE FUNCTION fn_eMailKontrol
(
    @EmailAddr VARCHAR(360) 
)
RETURNS BIT 

AS
BEGIN
    DECLARE @AlphabetPlus VARCHAR(255),
            @Max INT,
            @Pos INT, 
            @OK BIT;  
 
    IF @EmailAddr IS NULL
       OR @EmailAddr NOT LIKE '[0-9a-zA-Z]%@__%.__%'
       OR @EmailAddr LIKE '%@%@%'
       OR @EmailAddr LIKE '%..%'
       OR @EmailAddr LIKE '%.@'
       OR @EmailAddr LIKE '%@.'
       OR @EmailAddr LIKE '%@%.-%'
       OR @EmailAddr LIKE '%@%-.%'
       OR @EmailAddr LIKE '%@-%'
       OR CHARINDEX(' ', LTRIM(RTRIM(@EmailAddr))) > 0
        RETURN (0);

    DECLARE @AfterLastDot VARCHAR(360);
    DECLARE @AfterArobase VARCHAR(360);
    DECLARE @BeforeArobase VARCHAR(360);
    DECLARE @HasDomainTooLong BIT = 0;

    SET @AfterLastDot = REVERSE(SUBSTRING(REVERSE(@EmailAddr), 0, CHARINDEX('.', REVERSE(@EmailAddr))));
    IF LEN(@AfterLastDot) NOT
       BETWEEN 2 AND 17
        RETURN (0);

    SET @AfterArobase = REVERSE(SUBSTRING(REVERSE(@EmailAddr), 0, CHARINDEX('@', REVERSE(@EmailAddr))));
    IF LEN(@AfterArobase) NOT
       BETWEEN 2 AND 255
        RETURN (0);

    SELECT TOP 1
           @BeforeArobase = value
    FROM STRING_SPLIT(@EmailAddr, '@');
    IF LEN(@AfterArobase) NOT
       BETWEEN 2 AND 255
        RETURN (0);

    SELECT TOP 1
           @HasDomainTooLong = 1
    FROM STRING_SPLIT(@AfterArobase, '.')
    WHERE LEN(value) > 63;
    IF @HasDomainTooLong = 1
        RETURN (0);

    SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890!#$%&‘*+-/=?^_`.{|}~',
           @Max = LEN(@BeforeArobase),
           @Pos = 0,
           @OK = 1;


    WHILE @Pos < @Max AND @OK = 1
    BEGIN
        SET @Pos = @Pos + 1;
        IF @AlphabetPlus NOT LIKE '%' + SUBSTRING(@BeforeArobase, @Pos, 1) + '%'
            SET @OK = 0;
    END;

    IF @OK = 0
        RETURN (0);

    --Control de la partie domaine en detail
    SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890-.',
           @Max = LEN(@AfterArobase),
           @Pos = 0,
           @OK = 1;

    WHILE @Pos < @Max AND @OK = 1
    BEGIN
        SET @Pos = @Pos + 1;
        IF @AlphabetPlus NOT LIKE '%' + SUBSTRING(@AfterArobase, @Pos, 1) + '%'
            SET @OK = 0;
    END;

    IF @OK = 0
        RETURN (0);

    RETURN (1);

END;
GO


--Kullanımı


SELECT dbo.fn_eMailKontrol('y.selimkart@gmail.com')

When you create and run the above function, you will see a result similar to the one below.

As can be seen, it has been seen whether the e-mail address is a valid e-mail address.

Good luck to everyone in business and life.