sql - 如何在 PostgreSQL 中获取字符串中正则表达式匹配的位置？

Question

我有一个包含书名的表格，我想选择标题与正则表达式匹配的书籍，并根据正则表达式在标题中的位置对结果进行排序。

单个词搜索很容易。例如

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

在将查询发送到 DB 之前，我将在客户端搜索词的单词之间放置 .*，因此我将在此处使用准备好的正则表达式编写 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

但是如果搜索词有多个词，我想将包含搜索词中所有词的标题与它们之间的任何词进行匹配，并像以前一样按位置排序，所以我需要一个返回正则表达式位置的函数，我在官方 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??? 的函数，您有什么想法吗？

Answer 1

(许多)方法之一:删除从匹配项开始的字符串的其余部分并测量截断字符串的长度:

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

在 WHERE 子句中使用 ILIKE，因为这通常更快(这里也一样)。
还要注意 regexp_replace() 函数的第四个参数 ('i')，使其不区分大小写。

备选方案

根据评论中的要求。
同时演示如何首先对匹配项(和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'));

您可以在手册中找到上述所有内容的文档 here和 here .

-> SQLfiddle展示所有。