Regular expressions with SQL Server

All of us know the importance of using regular expressions to filter your results or check some specific rules if it exists or not for example to check if the inserted email in the correct format or to check if the inserted string has numbers or not ..Etc and of course you can use regular expressions in many ways with Microsoft technologies especially with SQL Server for example you can write CLR function or write it in your T-SQL code.

I will show you some examples of using Regular expressions with Arabic language in SQL Server:

--In this example we want to exclude from the results
--any Name has any charts excpet the arabic charts
DECLARE @Students TABLE (Name nvarchar(20))

INSERT @Students VALUES
(N'فادي'),
(N'أحمد ! فادي'),
(N'فادي أحمد عبدالوهابabc')

SELECT Name FROM @Students 
            WHERE Name Not Like N'%[^أ-ي]%'

And the result of the above query as the following:
Name
------------------------------
فادي

So the above result only returns the first inserted row because it's only the Name has Arabic charts only.


Note: PatIndex function Returns the starting position of the first occurrence of a pattern in a specified expression

--In this example we will see the power or PatIndex
--so we want to extract the arabic charts only
--from a sentense has arabic and english word
DECLARE @NAME nvarchar(50)
SET @NAME = N'Fadi فادي أحمد عبدالوهاب'

SELECT Substring(@Name,PATINDEX(N'%[أ-ي]%',@NAME),5000)

And the result of the above query as the following :
--------------------------------------------------
فادي أحمد عبدالوهاب

And the last example in .net code by using CLR fucntion

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fnVerfiyIsContainsArabic(string input)
    {
        //First Define an Object from Regex and pass your Pattern
        System.Text.RegularExpressions.Regex regObj = 
  new System.Text.RegularExpressions.Regex("[^أ-ي]");

        //Second check if the input text Is match the pattern or not
        bool flag = regObj.IsMatch(input);
        if (flag)
            //if it's matching print the following text 
            return "It's not only Contains Arabic Charts :" + input;
        else
            //if it's not matching print the following text
            return "It's only Contains Arabic Charts :" + input;
    }
};

Then run the following queries:

I hope you get some benefits from reading this article and good luck.