Calling .NET function from a SQL query

In this example we will write function for matching regular expression

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

namespace SqlRegularExpression
{
    public partial class RegularExpression
    {
        [SqlFunction]
        public static bool Like(string text, string pattern)
        {
            Match match = Regex.Match(text, pattern);
            return (match.Value != String.Empty);
        }
    }
}

In the SQl Server side, we will need to enable the CLR(Common Language Runtime) of .NET:

SELECT * FROM sys.configurations
where name = 'user connections'

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
CREATE ASSEMBLY SqlRegularExpression from 'C:\anyFolder\SqlRegularExpression.dll' WITH PERMISSION_SET = SAFE --- please check folder name
Go

In order to be able to use a method from any DLL, we should create a function or store procedure by using an external name:

CREATE FUNCTION RegExpLike(@Text nvarchar(max), @Pattern nvarchar(255))
RETURNS BIT AS
EXTERNAL NAME SqlRegularExpression.[SqlRegularExpression.RegularExpression].[Like]
go

Here it goes an example for a query:

select dbo.RegExpLike('Roy Jimenez', '^[a-zA-Z''.\s]{1,40}$');
go

You can use check constraints in a table to validate well constructed data in the database.
For example:

create table Users (
    login varchar(15) not null check ( dbo.RegExpLike(login, '^[A-Za-z0-9_]*$') = 1 ),
    name varchar(100) not null
)
go