Technical Article

SQL CLR Function for Email validation and string Split

,

Create two separate cs file for two above functions.

create DLL including 2 functions, lets say that is Splitter_email_validator.dll

Enable CLR in your database.

 

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Then Create assembly

CREATE ASSEMBLY String_splitter_and_Email_Validator
FROM C:\Email_validator\Splitter_email_validator.dll
WITH PERMISSION_SET = SAFE;

Note: Here C:\Email_validator\ is location of DLL.

 

Now create function in SQL.

First create string splitter:

CREATE FUNCTION [dbo].[string_splitter](@strtobeSplit [nvarchar](4000), @strSeparator [nvarchar](4000))
RETURNS TABLE (
[SeparatedString] [nvarchar](1000) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [String_splitter_and_Email_Validator ].[Emailvaliator_and_word_splitter].[string_splitter]

 

Now, create email validation function:

CREATE FUNCTION [dbo].[emailValidator](@emailAddress [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [String_splitter_and_Email_Validator ].[Emailvaliator_and_word_splitter].[emailValidator]

 

How to call this?

You can call these functions like other sql functions.

for example:

select * from [dbo].[string_splitter]('test,test1,test2' , ',')

When you run that statement you will get 3 rows as table:

test

test1

test2

Another function can be called like this:

select [dbo].[emailValidator]('bharatpanthee@gmail.com')

It will return 'valid'

if supplied email address is not vaid then it will return:

'Invalid'

 

 

 

 

/*C#.net function to split string by user defined separator*/using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
using System.Text;

public partial class Emailvaliator_and_word_splitter
{
 private class SplittedString
 {
 public SqlString splittedString;

 public SplittedString(SqlString splittedstring)
 {
 splittedString = splittedstring;
 }
 }
 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,
 FillRowMethodName = "Splitted_Values",
 TableDefinition = "SeparatedString nvarchar(1000)")]
 public static IEnumerable string_splitter(SqlString strtobeSplit, SqlString strSeparator)
 {
 char septr;
 septr = Convert.ToChar(strSeparator.ToString());
 ArrayList resultCollection = new ArrayList();
 string[] results;
 results = strtobeSplit.ToString().Split(septr);
 foreach (string str in results)
 {
 resultCollection.Add(new SplittedString(str));
 }
 return resultCollection;
 // return new SqlString("Hello");
 }
 public static void Splitted_Values(
 object objsplittedValue,
 out SqlString splittedValue)
 {
 SplittedString splittedStr = (SplittedString)objsplittedValue;
 splittedValue = splittedStr.splittedString;
 
 }
};

/* function to validate email address*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class Emailvaliator_and_word_splitter
{
 [Microsoft.SqlServer.Server.SqlFunction]
 public static SqlString emailValidator(SqlString emailAddress)
 {

 bool rtnVal = false;
 string pattern = @"[a-z]+[0-9]*@\w+[.](com|com.uk|com.np|net|com.in)$";
 rtnVal = Regex.IsMatch(emailAddress.ToString(), pattern);
 if (rtnVal)
 return "valid";
 else
 return "invalid";
 }
};
/*========================================*/

Rate

4 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (4)

You rated this post out of 5. Change rating