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.
While it’s been a few years since I’ve used SQL Server in a production environment, I can tell you that I was able to live without this functionality quite easily. Stuff like this would just be done in client code that sends data to the database.
And these days, I’d probably be more inclined to keep this stuff out of the database anyway and make it part of my unit tests. Sure, the UDF you wrote could be unit tested, but I’d like to verify that my client code is sending the proper data (ready to be inserted/updated) to the database.
Then again, I’m getting old too… š