在SQL查询中使用“LIKE”和“REGEXP”

I'm trying to use some regex on an expression where I have two conditions on the WHERE clause. The pattern I want to capture is 106 followed by any digit followed by a digit that must be either 3 or 4, i.e. 106[0-9][3-4]

我试图在表达式上使用一些正则表达式，其中我在WHERE子句上有两个条件。我要捕获的模式是106后跟任何数字后跟一个必须是3或4的数字，即106 [0-9] [3-4]

First, I tried this:

首先，我试过这个：

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND (ForestID REGEXP '106[0-9][3-4]')

This produced an error as below and it would be good to know why.

这产生了如下错误，知道原因会很好。

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'REGEXP'.

Next, I have tried this, which is now running but I am unsure about whether this is doing what I want it to do.

接下来，我尝试了这个，现在正在运行，但我不确定这是否正在做我想要它做的事情。

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND (ForestID LIKE '106[0-9][3-4]')

Would this do as I described above?

这会像我上面描述的那样吗？

3 个解决方案

#1

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

您不需要与托管代码交互，因为您可以使用LIKE：

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND ForestID LIKE '106[0-9][3-4]')

to make clear: SQL Server doesn't supports 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不支持没有托管代码的正则表达式。根据情况，LIKE运算符可以是一个选项，但它缺乏正则表达式提供的灵活性。

If you would like to have full regular expression functionality, try this.

如果您希望拥有完整的正则表达式功能，请尝试此操作。

#2

This is too long for a comment. You specify this:

这个评论太长了。你指定这个：

The pattern I want to capture is 106 followed by any digit followed by a digit that must be either 3 or 4, i.e. 106[0-9][3-4]

我要捕获的模式是106后跟任何数字后跟一个必须是3或4的数字，即106 [0-9] [3-4]

And then you give an example using a regular expression

然后使用正则表达式给出一个示例

WHERE ForestID REGEXP '106[0-9][3-4]'

Regular expressions match patterns anywhere inside a string. So, this will match '10603'. It will also match 'abc10694 def'. This is true of regular expressions in general, not merely one databases's implementation of them.

正则表达式匹配字符串内的任何位置。所以，这将匹配'10603'。它也将匹配'abc10694 def'。对于正则表达式而言，这是正确的，而不仅仅是一个数据库的实现。

If this is the behavior you want, then the corresponding LIKE (in SQL Server)` is:

如果这是你想要的行为，那么相应的LIKE（在SQL Server中）`是：

WHERE ForestID LIKE '%106[0-9][3-4]%'

If you only want 5-digit values, then the corresponding regular expression is:

如果您只想要5位数值，则相应的正则表达式为：

WHERE ForestID REGEXP '^106[0-9][3-4]$'

#3

Try Below

试试下面

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND ((ForestID LIKE '%106_3%' OR ForestID LIKE '%106_4%'))

#1