Introducción

¡Hola, chicos!
Me imagino que en algún momento ya debes haber necesitado revisar uno o más correos electrónicos en SQL Server para ver si el correo electrónico es válido.

Quizás ya hayas pensado en validar el dominio de correo electrónico, para saber si el dominio es válido. No me refiero a saber si el dominio fue ingresado correctamente y no tiene caracteres especiales, por ejemplo, sino a devolver si el dominio realmente existe: [correo electrónico protegido] es válido, pero [correo electrónico protegido] No lo es, por ejemplo.

Ahora veremos en esta publicación cómo es posible hacer esto usando SQL Server.

Importante: Para obtener más información sobre SQLCLR y comprender qué es y cómo funciona, lea el artículo. Introducción a SQL CLR (Common Language Runtime) en SQL Server o acceder Categoría CLR y ver todo lo que he hecho usando esta extensión de SQL Server.

Cómo validar el correo electrónico en SQL Server

En el artículo ya se demostró otra solución para la validación del correo electrónico. Validación de CPF, CNPJ, correo electrónico, teléfono y CEP en SQL Server, utilizando sólo la función SQL LIKE y algunas expresiones regulares. Pero la validación terminó no siendo del 100%, porque hubo algunos casos en los que correos electrónicos no válidos se catalogaban como válidos, como por ejemplo [correo electrónico protegido].

Los dominios no pueden tener guiones como primera o última letra y lo corregí en esta nueva función.

Código fuente de la función fncValida_Email.cs
Haga clic aquí para ver el código fuente

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;
        }

    }

}

Ejemplo de uso:

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

Importante: Para obtener más información sobre las expresiones regulares en SQL Server, lea el artículo. SQL Server: cómo utilizar expresiones regulares (RegExp) en su base de datos.

Cómo validar el dominio de correo electrónico en SQL Server

Otra necesidad que puede ser muy común es validar el dominio del correo electrónico, es decir, saber si [correo electrónico protegido] realmente existe o fue escrito incorrectamente.

Hay algunas reglas que siguen el RFC 5321 para definir que los dominios no pueden tener guiones como primera o última letra, por ejemplo, y podrían usarse para saber si un dominio está siguiendo estas reglas, pero esto no significa que el dominio realmente exista.

Para resolver este problema de una manera más precisa, decidí crear una función SQLCLR que realiza una solicitud al dominio usando el comando nslookup y devuelve si los registros MX devueltos desde ese dominio son válidos o no.

Código fuente de la función fncValida_Email_Dominio.cs
Haga clic aquí para ver el código fuente

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;
        }

    }

}

Ejemplo de uso

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

Importante: Como la función fncValida_Email_Dominio necesita acceder a recursos externos, la propiedad PERMISSION_SET al crear el ensamblaje no se puede establecer en SAFE. Intenté crearlo como EXTERNAL_ACCESS, pero debido al uso de la clase Proceso, de la biblioteca System.Diagnostics, el Ensamblaje comienza a usar código no administrado, y esto solo se permite con permiso INSEGURO al crear el Ensamblaje.

Debido al permiso INSEGURO, si la versión de SQL Server es inferior a 2017, la propiedad TRUSTWORTHY de la base de datos se cambiará a True, para que sea posible utilizar este ensamblado. Si la versión de SQL Server es igual o superior a 2017, esto no será necesario, ya que el ensamblado se agregará a la lista de ensamblajes_confiables.

Para comprender los riesgos de esta propiedad habilitada por TRUSTWORTHY, lea el artículo SQL Server: comprensión de los riesgos de la propiedad CONFIABLE habilitada en una base de datos o tomar mi curso Seguridad del servidor SQL.

Cómo crear estos objetos en mi base de datos

¿Te gustaron estas dos funciones? Así que implementémoslos en su base de datos y, para eso, preparé un script muy sencillo para usted.

Haga clic aquí para ver el código fuente
-----------------------------------------------------------------------
-- 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

Simplemente copie este código, péguelo en su SQL Server Management Studio (SSMS) y presione F5. Y eso es todo. Ahora simplemente comienza a usarlo de acuerdo con los ejemplos que demostré en esta publicación.

Espero que te haya gustado este consejo y nos vemos en el próximo post.