Regular Expression Replace (REGEXP_REPLACE) in SQL Server

visibility 74 access_time 25 days ago languageEnglish timeline Stats
timeline Stats
Page index 2.96
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

info Last modified by Kontext 25 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext
[Diagram] Azure Virtual Network Peering image
visibility 73
thumb_up 0
access_time 7 months ago
Azure Virtual Network Peering
Read Email from Microsoft 365 via Microsoft Graph API
visibility 2,594
thumb_up 2
access_time 9 months ago
Read Email from Microsoft 365 via Microsoft Graph API