在 SQL Server 中使用正则表达式 [英] Using RegEx in SQL Server

问题描述

我正在研究如何根据下面的 RegEx 设置/参数使用 RegEx 替换/编码文本:

I'm looking how to replace/encode text using RegEx based on RegEx settings/params below:

RegEx.IgnoreCase = True     
RegEx.Global = True     
RegEx.Pattern = "[^a-z\d\s.]+"   

我在 RegEx 上看到了一些例子，但对如何在 SQL Server 中以相同的方式应用它感到困惑.任何的意见都将会有帮助.谢谢.

I have seen some examples on RegEx, but confused as to how to apply it the same way in SQL Server. Any suggestions would be helpful. Thank you.

推荐答案

您不需要与托管代码交互，因为您可以使用 喜欢:

You do not need to interact with managed code, as you can use LIKE:

CREATE TABLE #Sample(Field varchar(50), Result varchar(50))
GO
INSERT INTO #Sample (Field, Result) VALUES ('ABC123 ', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123.', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123&', 'Match')
SELECT * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'
GO
DROP TABLE #Sample

当你的表达式以 + 结尾时，你可以使用 '%[^a-z0-9 .][^a-z0-9 .]%'

As your expression ends with + you can go with '%[^a-z0-9 .][^a-z0-9 .]%'

编辑:
明确说明:SQL Server 不支持没有托管代码的正则表达式.视情况而定，LIKE 运算符可以作为一个选项，但它缺乏正则表达式提供的灵活性.

EDIT:
To make it clear: SQL Server doesn't support regular expressions without managed code. Depending on the situation, the LIKE operator can be an option, but it lacks the flexibility that regular expressions provides.

这篇关于在 SQL Server 中使用正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！