如何在tsql查询中使用Regex

I have a column called link, which can hold different types of link. I'd like to retrieve only those that have a urls, i.e. www.google.com, so that I can apply something.

我有一个名为link的列,它可以包含不同类型的链接。我只想检索那些有网址的网址,即www.google.com,以便我可以应用一些东西。

SELECT *
FROM UserAlert
WHERE Link = ...// check whether it's a url

Thanks for helping

谢谢你的帮助

1 个解决方案

#1

This is almost 100% likely to be a job better suited to the front-end application, not the database. It will require code execution on the server.

这几乎100%可能是一个更适合前端应用程序的工作,而不是数据库。它需要在服务器上执行代码。

Here is a thread here on * about url detection regexes, from which you can select any of a number of reasonably good expressions: What is the best regular expression to check if a string is a valid URL?

这里是*上关于url检测正则表达式的一个线程,您可以从中选择任意一些相当好的表达式:检查字符串是否为有效URL的最佳正则表达式是什么?

To use regexes in MSSQL, you need to first use MSSQL 2005 or later. Assuming that is the case... you have to wrap regex functionality in a custom CLR object, enable CLR interaction on your whole database, and then you can use that custom CLR object in your WHERE clause.

要在MSSQL中使用正则表达式,您需要首先使用MSSQL 2005或更高版本。假设是这种情况......您必须在自定义CLR对象中包装正则表达式功能,在整个数据库上启用CLR交互,然后您可以在WHERE子句中使用该自定义CLR对象。

Here is a detailed article about doing exactly that with examples and step-by-step instructions.

这是一篇详细的文章,关于通过示例和分步说明来完成这项工作。

I hope you're REALLY SURE that you want code execution to be part of your database. Good luck!

我希望您确实希望代码执行成为数据库的一部分。祝你好运!

#1