Introduction

Hey guys!
I imagine that at some point, you must have already needed to check one or more emails in SQL Server to see if the email is valid.

You may have already thought about validating the email domain, to find out if the domain is valid. I'm not referring to knowing whether the domain was entered correctly and does not have special characters, for example, but rather returning whether the domain actually exists: [email protected] is valid, but [email protected] It's not, for example.

We will now see in this post how it is possible to do this using SQL Server

Important: To learn more about SQLCLR and understand what it is and how it works, read the article Introduction to SQL CLR (Common Language Runtime) in SQL Server or access CLR Category and see everything I've done using this SQL Server extension.

How to validate email in SQL Server

Another solution for email validation had already been demonstrated in the article Validating CPF, CNPJ, E-mail, Telephone and CEP in SQL Server, using only the SQL LIKE function and some regular expressions. But the validation ended up not being 100%, because there were some cases in which invalid emails were being classified as valid, such as [email protected].

Domains cannot have hyphens as the first or last letter and I corrected this in this new function.

Source code of the fncValida_Email.cs function
Click here to view the source code

using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncValida_Email(SqlString Ds_Email)
    {


        if (Ds_Email.IsNull)
            return false;


        var email = Ds_Email.Value.ToLower().Trim();

        if (string.IsNullOrEmpty(email))
            return false;


        if (!email.Contains("@"))
            return false;


        try
        {

            var regex = new Regex(@"^([a-zA-Z0-9]+([-._]{0,1}[a-zA-Z0-9]+)*)[-._]{0,1}@((([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])){1}|([a-zA-Z]+[\w-]+\.)+[a-zA-Z]{2,4})$");
            if (!regex.Match(email).Success)
                return false;

            if (!email.Contains("nao"))
                return true;

            return !email.Contains("tem") && !email.Contains("possui");

        }
        catch
        {
            return false;
        }

    }

}

Usage example:

SELECT dbo.fncValida_Email('[email protected]') -- 1
SELECT dbo.fncValida_Email('dirceu.dirceuresende.com') -- 0
SELECT dbo.fncValida_Email('dirceu@[email protected]') -- 0
SELECT dbo.fncValida_Email('[email protected]') -- 0
SELECT dbo.fncValida_Email('dirceu.dirceuresende-.com') -- 0
SELECT dbo.fncValida_Email('[email protected]') -- 0

Important: To learn more about regular expressions in SQL Server, read the article SQL Server – How to use regular expressions (RegExp) in your database.

How to validate email domain in SQL Server

Another need that can be very common is to validate the email domain, that is, to know if [email protected] actually exists or was typed incorrectly.

There are some rules that follow RFC 5321 to define that domains cannot have hyphens as the first or last letter, for example, and could be used to know if a domain is following these rules, but this does not mean that the domain actually exists.

To solve this problem in a more precise way, I decided to create a SQLCLR function that makes a request to the domain using the nslookup command and returns whether the MX records returned from that domain are valid or not.

Source code of the fncValida_Email_Dominio.cs function
Click here to view the source code

using System;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncValida_Email_Dominio(SqlString Ds_Email)
    {


        if (Ds_Email.IsNull)
            return false;


        var email = Ds_Email.Value.ToLower().Trim();

        if (string.IsNullOrEmpty(email))
            return false;


        if (!email.Contains("@"))
            return false;


        try
        {

            var palavras = email.Split('@');
            var dominio = palavras[1];

            var argumentos = $@"-type=MX {dominio}";

            using (var scriptProc = new Process { StartInfo = { FileName = "nslookup", UseShellExecute = false, Arguments = argumentos, RedirectStandardOutput = true, RedirectStandardError = true, StandardOutputEncoding = Encoding.GetEncoding(850), StandardErrorEncoding = Encoding.GetEncoding(850), CreateNoWindow = true } })
            {

                scriptProc.Start();

                var output = scriptProc.StandardOutput.ReadToEnd();

                return (output.IndexOf("mail exchanger = ", StringComparison.InvariantCultureIgnoreCase) >= 0);
                
            }

        }
        catch
        {
            return false;
        }

    }

}

Example of use

SELECT dbo.fncValida_Email_Dominio('[email protected]') -- 1
SELECT dbo.fncValida_Email_Dominio('[email protected]') -- 0
SELECT dbo.fncValida_Email_Dominio('[email protected]') -- 1
SELECT dbo.fncValida_Email_Dominio('[email protected]') -- 0

Important: As the fncValida_Email_Dominio function needs to access external resources, the PERMISSION_SET property when creating the Assembly cannot be set to SAFE. I tried to create it as EXTERNAL_ACCESS, but due to the use of the Process class, from the System.Diagnostics library, the Assembly starts to use unmanaged code, and this is only allowed with UNSAFE permission when creating the Assembly.

Due to the UNSAFE permission, if the SQL Server version is below 2017, the database's TRUSTWORTHY property will be changed to True, so that it is possible to use this Assembly. If the SQL Server version is equal to or above 2017, this will not be necessary, as the Assembly will be added to the list of trusted_assemblies.

To understand the risks of this TRUSTWORTHY-enabled property, read the article SQL Server – Understanding the risks of the TRUSTWORTHY property enabled in a database or take my course SQL Server Security.

How to create these objects in my database

Did you like these two functions? So let's implement them in your database and for that, I prepared a very easy script for you.

Click here to view the source code
-----------------------------------------------------------------------
-- HABILITA O CLR NA INSTÂNCIA
-----------------------------------------------------------------------

IF ((SELECT TOP(1) CONVERT(INT, [value]) FROM sys.configurations WHERE [name] = 'clr enabled') = 0)
BEGIN

    EXEC sys.sp_configure 'advanced options', 1;
    
    RECONFIGURE;
    
    EXEC sys.sp_configure 'clr enabled', 1;
    
    RECONFIGURE;
    
END


-----------------------------------------------------------------------
-- APAGA OS RECURSOS (CASO JÁ EXISTAM)
-----------------------------------------------------------------------

IF (OBJECT_ID('dbo.fncValida_Email') IS NOT NULL) DROP FUNCTION dbo.fncValida_Email
GO

IF (OBJECT_ID('dbo.fncValida_Email_Dominio') IS NOT NULL) DROP FUNCTION dbo.fncValida_Email_Dominio
GO



IF EXISTS(SELECT TOP(1) NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_Valida_Email')
BEGIN
    DROP ASSEMBLY [SQLCLR_Valida_Email]
END
GO


-----------------------------------------------------------------------
-- ASSINA O ASSEMBLY (SQL 2017+)
-----------------------------------------------------------------------

DECLARE @Nivel_Compatibilidade TINYINT = (SELECT [compatibility_level] FROM sys.databases WHERE [database_id] = 1)

IF (@Nivel_Compatibilidade >= 140) -- SQL 2017+
BEGIN
    
    EXEC('DECLARE @asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001DC08E620000000000000000E00022200B013000000E00000006000000000000E22D0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000902D00004F00000000400000D002000000000000000000000000000000000000006000000C000000582C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E80D000000200000000E000000020000000000000000000000000000200000602E72737263000000D0020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C42D0000000000004800000002000500D82200008009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005005B01000001000011000F00280500000A0B072C0C16280600000A0C38410100000F00280700000A6F0800000A6F0900000A0A06280A00000A0D092C0C16280600000A0C38190100000672010000706F0B00000A16FE01130411042C0C16280600000A0C38F9000000000006178D0C00000125161F409D6F0C00000A13051105179A130672050000701106280D00000A1307730E00000A256F0F00000A72190000706F1000000A00256F0F00000A166F1100000A00256F0F00000A11076F1200000A00256F0F00000A176F1300000A00256F0F00000A176F1400000A00256F0F00000A2052030000281500000A6F1600000A00256F0F00000A2052030000281500000A6F1700000A00256F0F00000A176F1800000A0013080011086F1900000A2611086F1A00000A6F1B00000A13091109722B000070196F1C00000A16FE0416FE01280600000A0CDE1811082C0811086F1D00000A00DC260016280600000A0CDE00082A00011C000002000F013241010D0000000000006100ED4E010B050000011B300200DA00000002000011000F00280500000A0B072C0C16280600000A0C38C00000000F00280700000A6F0800000A6F0900000A0A06280A00000A0D092C0C16280600000A0C38980000000672010000706F0B00000A16FE01130411042C0916280600000A0C2B7B0000724F000070731E00000A13051105066F1F00000A6F2000000A16FE01130611062C0916280600000A0CDE4E0672480200706F0B00000A16FE01130711072C0917280600000A0CDE310672500200706F0B00000A2D100672580200706F0B00000A16FE012B0116280600000A0CDE0B260016280600000A0CDE00082A00000110000000005E006FCD000B050000012202282100000A002A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000050020000237E0000BC0200008403000023537472696E677300000000400600006802000023555300A8080000100000002347554944000000B8080000C800000023426C6F620000000000000002000001471502000900000000FA0133001600000100000014000000020000000300000002000000210000000500000002000000010000000300000000005F0101000000000006007D005A0206009D005A020600540047020F007A0200000600040382010A00680004020A008E0189020A00220189020E00E70247020E007003A7020600250182010600E70182010E00B80147020600190151030600EC010A000600F9010A000600990182010600330082010E002C01A7020E00E101A702000000000100000000000100010001001000C60200001500010001005020000000009600C90188000100D421000000009600460188000200CC22000000008618410206000300000001005601000001005601090041020100110041020600190041020A0031004102060041007701200039000B0324004100CF002A0059001F022A00590089012A00590076032E0059009E0233005900170338005900FD023F004900410206004900AA01450069003F004A006900BB004F006900EF024A00690036034F00690027024F007100FB005400690007015A006900E1005A0069005D034F0049001D032000490023036000810029002A005900D900650091004C000600510041024A0051002C017900A100DB02200029004102060020002300C0002E000B008F002E00130098002E001B00B70040002300C00010006C00048000000000000000000000000000000000320100000200000000000000000000007F002000000000000200000000000000000000007F001400000000000200000000000000000000007F008201000000000000003C4D6F64756C653E0053797374656D2E494F0053797374656D2E44617461006D73636F726C69620052656164546F456E640049446973706F7361626C65007365745F46696C654E616D6500446973706F73650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007365745F5573655368656C6C45786563757465006765745F56616C756500496E6465784F66007365745F5374616E646172644572726F72456E636F64696E6700476574456E636F64696E67007365745F5374616E646172644F7574707574456E636F64696E670053716C537472696E67004D617463680053514C434C525F56616C6964615F456D61696C00666E6356616C6964615F456D61696C0044735F456D61696C0053514C434C525F56616C6964615F456D61696C2E646C6C006765745F49734E756C6C0053797374656D005472696D0053716C426F6F6C65616E00537472696E67436F6D70617269736F6E006765745F5374617274496E666F0050726F636573735374617274496E666F00666E6356616C6964615F456D61696C5F446F6D696E696F0047726F757000436861720053747265616D5265616465720054657874526561646572004D6963726F736F66742E53716C5365727665722E53657276657200546F4C6F776572007365745F52656469726563745374616E646172644572726F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300436F6E7461696E730053797374656D2E546578742E526567756C617245787072657373696F6E730055736572446566696E656446756E6374696F6E73006765745F537563636573730050726F63657373007365745F417267756D656E747300436F6E636174004F626A656374006F705F496D706C696369740053706C6974005374617274006765745F5374616E646172644F7574707574007365745F52656469726563745374616E646172644F75747075740053797374656D2E54657874007365745F4372656174654E6F57696E646F770052656765780049734E756C6C4F72456D707479000003400000132D0074007900700065003D004D005800200001116E0073006C006F006F006B007500700000236D00610069006C002000650078006300680061006E0067006500720020003D0020000081F75E0028005B0061002D007A0041002D005A0030002D0039005D002B0028005B002D002E005F005D007B0030002C0031007D005B0061002D007A0041002D005A0030002D0039005D002B0029002A0029005B002D002E005F005D007B0030002C0031007D0040002800280028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D00290029007B0031007D007C0028005B0061002D007A0041002D005A005D002B005B005C0077002D005D002B005C002E0029002B005B0061002D007A0041002D005A005D007B0032002C0034007D002900240001076E0061006F000007740065006D00000D70006F0073007300750069000000004DD048A52FF6E74087EBFFCAA5D3611E000420010108032000010520010111110F070A0E02111D02021D0E0E0E12250E03200002050001111D020320000E040001020E042001020E0620011D0E1D030500020E0E0E0420001235042001010E0420010102050001123908052001011239042000123D062002080E11450C07080E02111D020212290202052001124D0E08B77A5C561934E089060001111D11210801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000001DC08E6200000000020000001C010000742C0000740E0000525344533778CBBCD637684EAEF216F29E841DA901000000433A5C55736572735C41646D696E6973747261746F725C736F757263655C7265706F735C53514C434C525F56616C6964615F456D61696C5C53514C434C525F56616C6964615F456D61696C5C6F626A5C44656275675C53514C434C525F56616C6964615F456D61696C2E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B82D00000000000000000000D22D0000002000000000000000000000000000000000000000000000C42D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000740200000000000000000000740234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D4010000010053007400720069006E006700460069006C00650049006E0066006F000000B001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000050001800010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00560061006C006900640061005F0045006D00610069006C002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00560061006C006900640061005F0045006D00610069006C002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E43D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;

    DECLARE @clrName nvarchar(4000) = ''SQLCLR_Valida_Email''
    DECLARE @hash varbinary(64);

    SELECT @hash = HASHBYTES(''SHA2_512'', @asmBin);

    IF (EXISTS(SELECT TOP(1) NULL FROM sys.trusted_assemblies WHERE [hash] = @hash))
    BEGIN

        EXEC sys.sp_drop_trusted_assembly
            @hash = @hash
        
    END;
    
    EXEC sys.sp_add_trusted_assembly 
        @hash = @hash,
        @description = @clrName;')

END
GO


-----------------------------------------------------------------------
-- CRIA O ASSEMBLY
-----------------------------------------------------------------------

CREATE ASSEMBLY [SQLCLR_Valida_Email]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001DC08E620000000000000000E00022200B013000000E00000006000000000000E22D0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000902D00004F00000000400000D002000000000000000000000000000000000000006000000C000000582C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E80D000000200000000E000000020000000000000000000000000000200000602E72737263000000D0020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C42D0000000000004800000002000500D82200008009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005005B01000001000011000F00280500000A0B072C0C16280600000A0C38410100000F00280700000A6F0800000A6F0900000A0A06280A00000A0D092C0C16280600000A0C38190100000672010000706F0B00000A16FE01130411042C0C16280600000A0C38F9000000000006178D0C00000125161F409D6F0C00000A13051105179A130672050000701106280D00000A1307730E00000A256F0F00000A72190000706F1000000A00256F0F00000A166F1100000A00256F0F00000A11076F1200000A00256F0F00000A176F1300000A00256F0F00000A176F1400000A00256F0F00000A2052030000281500000A6F1600000A00256F0F00000A2052030000281500000A6F1700000A00256F0F00000A176F1800000A0013080011086F1900000A2611086F1A00000A6F1B00000A13091109722B000070196F1C00000A16FE0416FE01280600000A0CDE1811082C0811086F1D00000A00DC260016280600000A0CDE00082A00011C000002000F013241010D0000000000006100ED4E010B050000011B300200DA00000002000011000F00280500000A0B072C0C16280600000A0C38C00000000F00280700000A6F0800000A6F0900000A0A06280A00000A0D092C0C16280600000A0C38980000000672010000706F0B00000A16FE01130411042C0916280600000A0C2B7B0000724F000070731E00000A13051105066F1F00000A6F2000000A16FE01130611062C0916280600000A0CDE4E0672480200706F0B00000A16FE01130711072C0917280600000A0CDE310672500200706F0B00000A2D100672580200706F0B00000A16FE012B0116280600000A0CDE0B260016280600000A0CDE00082A00000110000000005E006FCD000B050000012202282100000A002A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000050020000237E0000BC0200008403000023537472696E677300000000400600006802000023555300A8080000100000002347554944000000B8080000C800000023426C6F620000000000000002000001471502000900000000FA0133001600000100000014000000020000000300000002000000210000000500000002000000010000000300000000005F0101000000000006007D005A0206009D005A020600540047020F007A0200000600040382010A00680004020A008E0189020A00220189020E00E70247020E007003A7020600250182010600E70182010E00B80147020600190151030600EC010A000600F9010A000600990182010600330082010E002C01A7020E00E101A702000000000100000000000100010001001000C60200001500010001005020000000009600C90188000100D421000000009600460188000200CC22000000008618410206000300000001005601000001005601090041020100110041020600190041020A0031004102060041007701200039000B0324004100CF002A0059001F022A00590089012A00590076032E0059009E0233005900170338005900FD023F004900410206004900AA01450069003F004A006900BB004F006900EF024A00690036034F00690027024F007100FB005400690007015A006900E1005A0069005D034F0049001D032000490023036000810029002A005900D900650091004C000600510041024A0051002C017900A100DB02200029004102060020002300C0002E000B008F002E00130098002E001B00B70040002300C00010006C00048000000000000000000000000000000000320100000200000000000000000000007F002000000000000200000000000000000000007F001400000000000200000000000000000000007F008201000000000000003C4D6F64756C653E0053797374656D2E494F0053797374656D2E44617461006D73636F726C69620052656164546F456E640049446973706F7361626C65007365745F46696C654E616D6500446973706F73650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007365745F5573655368656C6C45786563757465006765745F56616C756500496E6465784F66007365745F5374616E646172644572726F72456E636F64696E6700476574456E636F64696E67007365745F5374616E646172644F7574707574456E636F64696E670053716C537472696E67004D617463680053514C434C525F56616C6964615F456D61696C00666E6356616C6964615F456D61696C0044735F456D61696C0053514C434C525F56616C6964615F456D61696C2E646C6C006765745F49734E756C6C0053797374656D005472696D0053716C426F6F6C65616E00537472696E67436F6D70617269736F6E006765745F5374617274496E666F0050726F636573735374617274496E666F00666E6356616C6964615F456D61696C5F446F6D696E696F0047726F757000436861720053747265616D5265616465720054657874526561646572004D6963726F736F66742E53716C5365727665722E53657276657200546F4C6F776572007365745F52656469726563745374616E646172644572726F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300436F6E7461696E730053797374656D2E546578742E526567756C617245787072657373696F6E730055736572446566696E656446756E6374696F6E73006765745F537563636573730050726F63657373007365745F417267756D656E747300436F6E636174004F626A656374006F705F496D706C696369740053706C6974005374617274006765745F5374616E646172644F7574707574007365745F52656469726563745374616E646172644F75747075740053797374656D2E54657874007365745F4372656174654E6F57696E646F770052656765780049734E756C6C4F72456D707479000003400000132D0074007900700065003D004D005800200001116E0073006C006F006F006B007500700000236D00610069006C002000650078006300680061006E0067006500720020003D0020000081F75E0028005B0061002D007A0041002D005A0030002D0039005D002B0028005B002D002E005F005D007B0030002C0031007D005B0061002D007A0041002D005A0030002D0039005D002B0029002A0029005B002D002E005F005D007B0030002C0031007D0040002800280028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D0029005C002E0028005B0030002D0031005D003F005B0030002D0039005D007B0031002C0032007D007C00320035005B0030002D0035005D007C0032005B0030002D0034005D005B0030002D0039005D00290029007B0031007D007C0028005B0061002D007A0041002D005A005D002B005B005C0077002D005D002B005C002E0029002B005B0061002D007A0041002D005A005D007B0032002C0034007D002900240001076E0061006F000007740065006D00000D70006F0073007300750069000000004DD048A52FF6E74087EBFFCAA5D3611E000420010108032000010520010111110F070A0E02111D02021D0E0E0E12250E03200002050001111D020320000E040001020E042001020E0620011D0E1D030500020E0E0E0420001235042001010E0420010102050001123908052001011239042000123D062002080E11450C07080E02111D020212290202052001124D0E08B77A5C561934E089060001111D11210801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000001DC08E6200000000020000001C010000742C0000740E0000525344533778CBBCD637684EAEF216F29E841DA901000000433A5C55736572735C41646D696E6973747261746F725C736F757263655C7265706F735C53514C434C525F56616C6964615F456D61696C5C53514C434C525F56616C6964615F456D61696C5C6F626A5C44656275675C53514C434C525F56616C6964615F456D61696C2E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B82D00000000000000000000D22D0000002000000000000000000000000000000000000000000000C42D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000740200000000000000000000740234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D4010000010053007400720069006E006700460069006C00650049006E0066006F000000B001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000050001800010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00560061006C006900640061005F0045006D00610069006C002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00560061006C006900640061005F0045006D00610069006C002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E43D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = UNSAFE;
GO



-----------------------------------------------------------------------
-- CRIA OS OBJETOS
-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[fncValida_Email] (@Ds_Email NVARCHAR (MAX))
RETURNS BIT
AS
EXTERNAL NAME [SQLCLR_Valida_Email].[UserDefinedFunctions].[fncValida_Email]
GO


CREATE FUNCTION [dbo].[fncValida_Email_Dominio] (@Ds_Email NVARCHAR (MAX))
RETURNS BIT
AS
EXTERNAL NAME [SQLCLR_Valida_Email].[UserDefinedFunctions].[fncValida_Email_Dominio]
GO

Just copy this code, paste it into your SQL Server Management Studio (SSMS) and press F5. And that's it. Now just start using it according to the examples I demonstrated in this post.

I hope you liked this tip and see you in the next post.