How to get position of regexp match in string in PostgreSQL?

database, postgresql, regex, sql

Solution

One way (of many) to do this: Remove the rest of the string beginning at the match and measure the length of the truncated string:

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

Using `ILIKE` in the WHERE clause, since that is typically faster (and does the same here). Also note the fourth parameter to the `regexp_replace()` function (`'i'`), to make it case insensitive.

Alternatives

As per request in the comment. At the same time demonstrating how to sort matches 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'));

You can find documentation for all of the above in the manual here and here.

-> SQLfiddle demonstrating all.

Problem

I have a table with book titles and I want to select books that have title matching a regexp and to order results by the position of the regexp match in title. It's easy for a single-word searches. E.g. ``` TABLE book id title 1 The Sun 2 The Dead Sun 3 Sun Kissed ``` I'm going to put `.*` between words in client's search term before sending query to DB, so I'd write SQL with prepared regexps here. ``` 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 ``` But if search term has more than one word I want to match titles that have all words from search term with anything between them, and sort by the position like before, so I need a function that returns a position of regexp, I didn't find an appropriate one in official PostgreSQL docs. ``` 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 ``` I didn't find any function similar to ???REGEXP_POSITION_FUNCTION???, do you have any ideas?

Original source

Related problems