PostgreSQL 8.4中的字符串匹配 [英] String matching in PostgreSQL 8.4

问题描述

我需要在PostgreSQL 8.4中实现一个正则表达式（据我了解）匹配。似乎正则表达式匹配仅在9.0+中可用。

I need to implement a regular expression (as I understand) matching in PostgreSQL 8.4. It seems regular expression matching are only available in 9.0+.

我的需要是：

当我给输入 14.1 我需要得到以下结果：

When I give an input 14.1 I need to get these results:

14.1.1
14.1.2
14.1.Z
...

但是排除：

14.1.1.1
14.1.1.K
14.1.Z.3.A
...

模式不限于单个字符。总是有可能出现这样的模式： 14.1.1.2K ， 14.1.Z.13.A2 等，因为该模式是由用户提供的。该应用程序无法控制模式（不是版本号）。

The pattern is not limited to a single character. There is always a possibility that a pattern like this will be presented: 14.1.1.2K, 14.1.Z.13.A2 etc., because the pattern is provided the user. The application has no control over the pattern (it's not a version number).

任何想法如何在Postgres 8.4中实现吗？

Any idea how to implement this in Postgres 8.4?

又一个问题解决了我的问题在存储过程内的Postgres 8.4中转义LIKE模式或regexp字符串

After one more question my issue was solved Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure

推荐答案

首先，正则表达式匹配实际上已经存在于Postgres中，至少从7.1版本开始就如此。使用这些运算符：

First of all, regular expression matching has been in Postgres practically for ever, at least since version 7.1. Use the these operators:

~ !~ ~* !~*

dba.SE概述：

• 与LIKE，SIMILAR TO或PostgreSQL中的正则表达式匹配的模式

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

在您的情况下，似乎不允许出现另一个点：

The point in your case seems to be, that another dot is not allowed:

SELECT *
FROM   tbl
WHERE  version LIKE '14.1.%'        -- for performance
AND    version ~ '^14\.1\.[^.]+$';  -- for correct result

> SQL提琴。

Like 表达式是多余的，但是即使没有索引，它也可以显着提高性能。但是，您当然应该有一个索引。

The LIKE expression is redundant, but it is going to improve performance dramatically, even without index. But you should have an index, of course.

Like 表达式可以使用基本的 text_pattern_ops 索引，而正则表达式至少在Postgres 8.4中不能。

The LIKE expression can use a basic text_pattern_ops index, while the regular expression cannot, at least in Postgres 8.4.

• PostgreSQL相似的查询性能差异

• PostgreSQL LIKE query performance variations

[^。] 是一个字符类，不包括点（。）。因此，允许使用任何其他字符，只允许输入更多的点。

[^.] in the regex pattern is a character class that excludes the dot (.). So any other character is allowed, just no more dots.

为此目的，可以挤出最佳性能特定查询可以添加专门的索引：

To squeeze out top performance for this particular query you could add a specialized index:

CREATE INDEX tbl_specail_idx ON tbl
((length(version) - length(replace(version, '.', ''))), version text_pattern_ops);

并使用与上面相同的匹配查询，只需将最后一行替换为：

And use a matching query, the same as above, just replace the last line with:

AND   length(version) - length(replace(version, '.', '')) = 2

SQL小提琴。

这篇关于PostgreSQL 8.4中的字符串匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！