Kontext Kontext / SQL Server

Regular Expression Replace (REGEXP_REPLACE) in SQL Server

event 2022-06-04 visibility 20,372 comment 0 insights toc
insights Stats
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]
alter database [yourdb] set trustworthy on;

EXEC sp_configure 'clr enabled', 1

CREATE ASSEMBLY CLRFunctions FROM 'F:\Projects\CLRFunction\CLRFunctions.dll'

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

Create the function

Run the following SQL statements to create the function:

    @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. 


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

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

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts