Views: 1.015 views
Introduction
Speak guys!
I imagine that at some point, you must have had to check one or more emails in SQL Server, to see if the email is valid.
You must have already thought about validating the email domain, to know if the domain is valid. I am not referring to knowing if the domain was typed correctly and does not have special characters, for example, but to return if the domain really exists: emai[email protected] is valid, but [email protected] it is not, for example.
We will see now in this post, how it is possible to do this by SQL Server
Important: To learn more about SQLCLR and understand what it is and how it works, read the article Introduction to SQL Common Language Runtime (CLR) in SQL Server or go to CLR Category and see everything I've done using this SQL Server extension.
How to validate email in SQL Server
I had already demonstrated another solution for email validation in the article Validating CPF, CNPJ, Email, Phone, and Zip Code 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 where invalid emails were being classified as valid, such as [email protected].
Domains cannot have hyphens as the first or last letter and I fixed this in this new function.
Source code of the fncValida_Email.cs function
Click here to view the source code
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; } } } |
Example of use:
Transact-SQL
| 1 2 3 4 5 6 | SELECT dbo.fncValida_Email('dirceu.dirceuresende.com') -- 0 SELECT dbo.fncValida_Email('dirceu.dirceuresende-.com') -- 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 quite common is to validate the e-mail 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 a first or last letter, for example and could be used to know if a domain is following these rules, but that doesn't mean that the domain actually exists.
To solve this problem in a more precise way, I decided to create an 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
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; } } } |
Example of use
Transact-SQL
| 1 2 3 4 |
Important: As the function fncValida_Email_Dominio needs to access external resources, the PERMISSION_SET property on assembly creation cannot be set to SAFE. I tried to create as EXTERNAL_ACCESS, but due to the use of the Process class, from the System.Diagnostics library, the assembly starts using unmanaged code, and this is only allowed with the UNSAFE permission in the assembly creation.
Due to the UNSAFE permission, if the SQL Server version is below 2017, the TRUSTWORTHY property of the database will be changed to True, so that this Assembly can be used. If the SQL Server version is equal to or above 2017, this is not necessary as the assembly will be added to the list of trusted_assemblies.
To understand the risks of this enabled TRUSTWORTHY property, read the article SQL Server - Understanding the Risks of 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 codeTransact-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 |
Just copy this code, paste it into your SQL Server Management Studio (SSMS) and hit F5. And that's all. Now just start using as the examples I showed in this post.
I hope you enjoyed this tip and see you in the next post.
generates too much
Thank you.