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'
