如何在PostgreSQL中获得regexp匹配的字符串的位置?[英] How to get position of regexp match in string in PostgreSQL?

本文是小编为大家收集整理的关于如何在PostgreSQL中获得regexp匹配的字符串的位置?的处理方法,想解了如何在PostgreSQL中获得regexp匹配的字符串的位置?的问题怎么解决?如何在PostgreSQL中获得regexp匹配的字符串的位置?问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一张带有书籍的桌子,我想选择具有匹配式式标题的书籍,并按照标题中的regexp匹配的位置订购结果.

单词搜索很容易.例如

TABLE book
id   title
1    The Sun
2    The Dead Sun
3    Sun Kissed

在将查询发送到db之前,我将在客户端搜索词中的单词之间放置.*,所以我会在此处使用准备好的Regexps编写SQL.

SELECT book.id, book.title FROM book
    WHERE book.title ~* '.*sun.*'
    ORDER BY COALESCE(NULLIF(position('sun' in book.title), 0), 999999) ASC;

RESULT
id   title
3    Sun Kissed
1    The Sun
2    The Dead Sun

但是,如果搜索词有多个单词,我想匹配将所有单词与搜索词之间的单词与它们之间的任何单词匹配,并且像以前一样按位置进行排序,因此我需要一个函数,以返回Regexp的位置,我在官方的Postgresql文档中找不到合适的一个.

TABLE books
id   title
4    Deep Space Endeavor
5    Star Trek: Deep Space Nine: The Never Ending Sacrifice
6    Deep Black: Space Espionage and National Security

SELECT book.id, book.title FROM book
    WHERE book.title ~* '.*deep.*space.*'
    ORDER BY ???REGEXP_POSITION_FUNCTION???('.*deep.*space.*' in book.title);

DESIRED RESULT
id   title
4    Deep Space Endeavor
6    Deep Black: Space Espionage and National Security
5    Star Trek: Deep Space Nine: The Never Ending Sacrifice

我找不到类似于regexp_position_function的功能?

推荐答案

(许多人)做到这一点:删除从比赛开始的其余字符串,并测量截断的字符串的长度:

SELECT id, title
FROM   book
WHERE  title ILIKE '%deep%space%'
ORDER  BY length(regexp_replace(title, 'deep.*space.*', '','i'));

在where子句中使用ILIKE,因为这通常更快(在此处使用相同).
还要注意regexp_replace()函数('i')的第四个参数,以使其变得不敏感.

替代品

根据评论中的要求.
同时,演示如何对进行排序 first(and NULLS LAST ).

SELECT id, title
      ,substring(title FROM '(?i)(^.*)deep.*space.*') AS sub1
      ,length(substring(title FROM '(?i)(^.*)deep.*space.*')) AS pos1

      ,substring(title FROM '(?i)^.*(?=deep.*space.*)') AS sub2
      ,length(substring(title FROM '(?i)^.*(?=deep.*space.*)')) AS pos2

      ,substring(title FROM '(?i)^.*(deep.*space.*)') AS sub3
      ,position((substring(title FROM '(?i)^.*(deep.*space.*)')) IN title) AS p3

      ,regexp_replace(title, 'deep.*space.*', '','i') AS reg4
      ,length(regexp_replace(title, 'deep.*space.*', '','i')) AS pos4
FROM   book
ORDER  BY title ILIKE '%deep%space%' DESC NULLS LAST
         ,length(regexp_replace(title, 'deep.*space.*', '','i'));

您可以在手册中找到以上所有文档的文档和在这里/p>

- > sqlfiddle p>

其他推荐答案

另一种做到这一点的方法是首先获得图案的文字匹配,然后找到字面匹配的位置:

strpos(input, (regexp_match(input, pattern, 'i'))[1]);

或在这种情况下:

SELECT   id, title
FROM     book
ORDER BY strpos(book.title, (regexp_match(book.title, '.*deep.*space.*', 'i'))[1]);

但是,几乎没有警告:

  1. 这不是很有效,因为它将两次扫描输入字符串.

  2. 这将忽略Lookaround(LookBehind,LookAhead)的约束,因为在模式匹配之前,字面匹配可以多次出现. 例如:对于输入'aba'和模式'(?<=b)a',strpos将返回1(对于第1 'a'),尽管实际位置应为3(对于第二'a').

顺便说一句,您可能应该使用贪婪的量词,并尽可能多地缩小角色类,而不是.*来提高性能(例如

本文地址:https://www.itbaoku.cn/post/1763853.html