Visualizações: 1.500 views
Neste artigo
ToggleIntrodução
Fala pessoal!
Imagino que em algum momento, você já deve ter precisado verificar um ou mais e-mails no SQL Server, para saber se o e-mail é válido.
Vocês já devem ter pensado validar o domínio do e-mail, para saber se o domínio é válido. Não estou me referindo a saber se o domínio foi digitado corretamente e não possui caracteres especiais, por exemplo, e sim retornar se o domínio realmente existe: [email protected] é válido, mas [email protected] não é, por exemplo.
Veremos agora nesse post, como é possível fazer isso pelo SQL Server
Importante: Para saber mais sobre SQLCLR e entender o que ele é e como ele funciona, leia o artigo Introdução ao SQL CLR (Common Language Runtime) no SQL Server ou acesse a Categoria de CLR e veja tudo o que já fiz utilizando essa extensão do SQL Server.
Como validar e-mail no SQL Server
Já havia demonstrado outra solução para validação de e-mail no artigo Validando CPF, CNPJ, E-mail, Telefone e CEP no SQL Server, utilizando apenas a função LIKE do SQL e algumas expressões regulares. Mas a validação acabou não ficando 100%, porque existiam alguns casos em que e-mails inválidos estavam sendo classificados como válidos, como [email protected].
Domínios não podem ter hifens como primeira ou última letra e isso eu corrigi nessa nova função.
Código-fonte da função fncValida_Email.cs
Clique aqui para visualizar o código-fonte
C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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; } } } |
Exemplo de uso:
Transact-SQL
|
1 2 3 4 5 6 |
SELECT dbo.fncValida_Email('dirceu.dirceuresende.com') -- 0 SELECT dbo.fncValida_Email('dirceu.dirceuresende-.com') -- 0 |
Importante: Para saber mais sobre expressões regulares no SQL Server, leia o artigo SQL Server – Como utilizar expressões regulares (RegExp) no seu banco de dados.
Como validar o domínio do e-mail no SQL Server
Outra necessidade que pode ser bem comum, é a de validar o domínio do e-mail, isto é, saber se [email protected] realmente existe ou foi digitado incorretamente.
Existem algumas regras que seguem a RFC 5321 para definir que domínios não podem ter hifens como primeira ou última letra, por exemplo e poderiam ser usadas para saber se um domínio está seguindo essas regras, mas isso não quer dizer que o domínio realmente existe.
Para resolver esse problema de uma forma mais precisa, resolvi criar uma função SQLCLR que faça uma requisição ao domínio utilizando o comando nslookup e retorna se os registros MX retornados desse domínio são válidos ou não.
Código-fonte da função fncValida_Email_Dominio.cs
Clique aqui para visualizar o código-fonte
C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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; } } } |
Exemplo de utilização
Transact-SQL
|
1 2 3 4 |
Importante: Como a função fncValida_Email_Dominio precisa de acessar recursos externos, a propriedade PERMISSION_SET na criação do Assembly não pode ser definida como SAFE. Eu tentei criar como EXTERNAL_ACCESS, mas devido ao uso da classe Process, da biblioteca System.Diagnostics, o Assembly passa a usar código não-gerenciado, e isso só é permitido com a permissão UNSAFE na criação do Assembly.
Por conta da permissão UNSAFE, caso a versão do SQL Server seja abaixo da 2017, a propriedade TRUSTWORTHY do banco será alterada para True, para que seja possível utilizar esse Assembly. Caso a versão do SQL Server igual ou acima da 2017, isso não será necessário, pois o Assembly será adicionado na lista de trusted_assemblies.
Para entender os riscos dessa propriedade TRUSTWORTHY habilitada, leia o artigo SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database ou faça o meu curso de Segurança de SQL Server.
Como criar esses objetos no meu banco de dados
Gostou dessas duas funções? Então vamos implementá-las no seu banco de dados e para isso, preparei um script bem fácil para você.
Clique aqui para visualizar o código-fonteTransact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
----------------------------------------------------------------------- -- 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 |
Basta copiar esse código, colar no seu SQL Server Management Studio (SSMS) e apertar F5. E é só isso. Agora é só começar a usar conforme os exemplos que demonstrei nesse post.
Espero que tenham gostado dessa dica e até o próximo post.
Portuguese 
English
Gera demais
Muito obrigado.