My last post was about SQL Server. Even 10+ years later, I’m continually amazed by features which are daily use features in MySQL and Postgresql which are missing from MS SQL Server.
Regular expression matching and replacing is a severely lacking feature.
Sql Server 2005 introduced a means to write user defined functions in .NET code, so this method is uploaded to a sql server and exposed as a function.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement)
{
var result = System.Text.RegularExpressions.Regex.Replace(input.Value, pattern.Value, replacement.Value);
return new SqlString(result);
}
Now you can use it in select statements.
SELECT Name, dbo.RegexReplace(Name, N'(?:\d+-)?\d+PPM’, N”) AS e
FROM Table2
Or use it to update tables
update table2 set name=dbo.RegexReplace(Name, N'(?:\d+-)?\d+PPM’, N”)
I have absolutely no idea how any database developer could live without this kind of functionality.