The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression.
This Q/A from stackoverflow rightly concludes that if you query must rely on regular expressions you shuould use CLR. This Readgate article elaborates this approach more. So one of my colleagues and I proposed this solution, but my other colleague categorically stated that using CLR here would be a huge risk to security and stability, and using external script (Python or R) is more secure.
This seems to be dubious claim, since the user code in the CLR can be managed, so perhaps the opposite is true, but I was not able to persuade my colleague.
In my other question which I wrote in my desperation because I was forced to use external script and still produce a blazing fast query. SQLpro user states in his comment that:
Using Python or R can be worst in terms of security rather than using CLR!
Which I tend to believe.
So I have two questions:
Which Regexp solution is more secure external script or CLR based (as described here)? And why?
I also proposed to run the python code on the same Windows Server (must be the same server, because of a policy) but with python intrepeter installed on the OS. Because the results are exported into CSV files either way and stored in the SQL Server. So then I would able to use Python's multiprocessing module to achieve the right performance. The answer was the same that running Python inside SQL Server is more secure than in a outside application. Which is also a questionable claim.