Regular Expression Replace (REGEXP_REPLACE) in SQL Server

SQL Server doesn't include a built-in function like REGEXP_REPLACE to replace string with regular expressions. This article provides one approach of using CLR (.NET Functions) to implement. CLR function is supported by all SQL Server on-premise versions and Azure SQL Managed Instance. This means that Azure SQL databases are not supported thus this approach doesn't apply to it.

Create CLR library

Create a C# file named CLRFunctions.cs and add the following content:

using Microsoft.SqlServer.Server;

namespace CLRFunctions
{
    public class Regex
    {
        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static string Replace(string input, string pattern, string replacement)
        {
            if (input == null || pattern == null || replacement == null)
                return input;
            return System.Text.RegularExpressions.Regex.Replace(input, pattern, replacement);

        }
    }
}

Compile it to a library using the following command:

csc.exe /t:library /out:CLRFunctions.dll CLRFunctions.cs  

If you cannot find csc.exe, make sure you have Visual Studio installed and use Developer Command Prompt. 

Add the assembly

Run the following SQL statements against your database to enable CLR and register the assembly:

use [yourdb]
go
alter database [yourdb] set trustworthy on;
go

EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
go

CREATE ASSEMBLY CLRFunctions FROM 'F:\Projects\CLRFunction\CLRFunctions.dll'
WITH PERMISSION_SET = SAFE; 

Remember to change database name and path to your assembly accordingly.

Create the function

Run the following SQL statements to create the function:

CREATE FUNCTION REGEXP_REPLACE(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max)
) RETURNS nvarchar  (max)
AS EXTERNAL NAME CLRFunctions.[CLRFunctions.Regex].Replace; 

Use the function

You can then use the function in your T-SQL statements:

SELECT dbo.REGEXP_REPLACE('ABC_DEF_1234','(?<prefix>[a-zA-Z_]+)(?<number>\d+)','${prefix}____${number}____');

The example uses named groups to replace. The following is the output:

For Azure SQL databases

AS CLR feature is not supported on Azure SQL databases (except for managed instance), you can use external programs like Python, .NET or any other preferable language/framework to update the content in the database. For example, establish connections to Azure SQL, read the content into memory and do the regular expression replace and finally write the result into database using UPDATE statements. 

References

Regex.Replace Method (System.Text.RegularExpressions) | Microsoft Docs

Common Language Runtime (CLR) Overview - SQL Server | Microsoft Docs