T-SQL 中的 SQLServer 正则表达式

是否有任何用 T-SQL 编写的用于 SQL Server 的正则表达式库(没有 CLR，没有扩展 SP，纯 T-SQL) ，并且应该可以使用共享主机？

编辑:

  • 谢谢，我知道 PATINDEX，LIKE，xp_和 CLR 解决方案
  • 我也知道它不是正则表达式的最佳位置，问题是理论上的:)
  • 降低功能也是可以接受的
171296 次浏览
小开

有一些基本的模式匹配可以通过使用 LIKE 来实现，其中% 匹配任意数量和字符组合，_ 匹配任意一个字符，而[ abc ]可以匹配 a、 b 或 c。. 还有更多关于 MSDN 网站的信息。

小开

PATINDEX函数怎么样？

TSQL 的模式匹配不是一个完整的正则表达式库，但它提供了基础知识。

(来自网上图书)

Wildcard  Meaning
% Any string of zero or more characters.


_ Any single character.


[ ] Any single character within the specified range
(for example, [a-f]) or set (for example, [abcdef]).


[^] Any single character not within the specified range
(for example, [^a - f]) or set (for example, [^abcdef]).
小开

可以使用 OLE 自动化来使用 VBScript 正则表达式特性。这比创建和维护程序集的开销要好得多。请确保您通过评论部分得到一个更好的修改版本的主要一个。

Http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx

DECLARE @obj INT, @res INT, @match BIT;
DECLARE @pattern varchar(255) = '<your regex pattern goes here>';
DECLARE @matchstring varchar(8000) = '<string to search goes here>';
SET @match = 0;


-- Create a VB script component object
EXEC @res = sp_OACreate 'VBScript.RegExp', @obj OUT;


-- Apply/set the pattern to the RegEx object
EXEC @res = sp_OASetProperty @obj, 'Pattern', @pattern;


-- Set any other settings/properties here
EXEC @res = sp_OASetProperty @obj, 'IgnoreCase', 1;


-- Call the method 'Test' to find a match
EXEC @res = sp_OAMethod @obj, 'Test', @match OUT, @matchstring;


-- Don't forget to clean-up
EXEC @res = sp_OADestroy @obj;

如果您得到 SQL Server blocked access to procedure 'sys.sp_OACreate'...错误，使用 sp_reconfigure启用 Ole Automation Procedures。(是的，不幸的是，这是一个服务器级别的变化!)

关于 Test方法的更多信息可以在 给你中找到

编程愉快

小开

如果其他人仍然在看这个问题，http://www.sqlsharp.com/是一个免费的，添加正则表达式 < strong > CLR 函数的简单方法 进入您的数据库。

小开

如果有人对在 CLR 中使用正则表达式感兴趣，这里有一个解决方案。下面的函数(C # 。Net 4.5)如果模式匹配，返回1; 如果模式不匹配，返回0。我使用它在子查询中标记行。SQLfunction 属性告诉 SQL 服务器此方法是 SQL 服务器将使用的实际 UDF。将文件保存为 dll，保存在可以从管理工作室访问它的位置。

// default using statements above
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;


namespace CLR_Functions
{
public class myFunctions
{
[SqlFunction]
public static SqlInt16 RegexContain(SqlString text, SqlString pattern)
{
SqlInt16 returnVal = 0;
try
{
string myText = text.ToString();
string myPattern = pattern.ToString();
MatchCollection mc = Regex.Matches(myText, myPattern);
if (mc.Count > 0)
{
returnVal = 1;
}
}
catch
{
returnVal = 0;
}


return returnVal;
}
}
}

在管理工作室中，通过可编程性——程序集——新程序集导入 dll 文件

然后运行以下查询:

CREATE FUNCTION RegexContain(@text NVARCHAR(50), @pattern NVARCHAR(50))
RETURNS smallint
AS
EXTERNAL NAME CLR_Functions.[CLR_Functions.myFunctions].RegexContain

然后，您应该可以通过存储程序集的数据库完全访问该函数。

然后像这样在查询中使用:

SELECT *
FROM
(
SELECT
DailyLog.Date,
DailyLog.Researcher,
DailyLog.team,
DailyLog.field,
DailyLog.EntityID,
DailyLog.[From],
DailyLog.[To],
dbo.RegexContain(Researcher, '[\p{L}\s]+') as 'is null values'
FROM [DailyOps].[dbo].[DailyLog]
) AS a
WHERE a.[is null values] = 0
小开

如果使用的是 SQLServer2016或更高版本，则可以使用 sp_execute_external_script和 R。它具有用于正则表达式搜索的函数，例如 grep和 grepl。

这里有一个电子邮件地址的例子。我将通过 SQL Server 数据库引擎查询一些“人”，将这些人的数据传递给 R，让 R 决定哪些人的电子邮件地址无效，并让 R 将这部分人传递回 SQL Server。“ people”来自 [WideWorldImporters]示例数据库中的 [Application].[People]表。它们作为一个名为 InputDataSet的数据框架传递给 R 引擎。R 使用带有“ not”操作符的 grepl 函数(叹号!)查找哪些人的电子邮件地址与 RegEx 字符串搜索模式不匹配。

EXEC sp_execute_external_script
@language = N'R',
@script = N' RegexWithR <- InputDataSet;
OutputDataSet <- RegexWithR[!grepl("([_a-z0-9-]+(\\.[_a-z0-9-]+)*@[a-z0-9-]+(\\.[a-z0-9-]+)*(\\.[a-z]{2,4}))", RegexWithR$EmailAddress), ];',
@input_data_1 = N'SELECT PersonID, FullName, EmailAddress FROM Application.People'
WITH RESULT SETS (([PersonID] INT, [FullName] NVARCHAR(50), [EmailAddress] NVARCHAR(256)))

注意，必须在 SQLServer 主机上安装适当的特性。对于 SQLServer2016，它被称为“ SQLServerR 服务”。对于 SQLServer2017，它被重命名为“ SQLServer 机器学习服务”。

结束语 微软的 SQL (T-SQL)实现不支持正则表达式。对于 OP 来说，这种建议的解决方案可能并不比使用 CLR 存储过程更合适。但它确实提供了另一种解决问题的方法。


提交答案