Find the position of a string match

Question

I have a column in Postgres like:

name
abc def ghi
lmnop qrst uvw
xyz

Is there any way I get the match and the position of the match?
For example
If I search for 'ef' then I should get the first by using a query like:

select * from table where name like '%ef%';

and the position of the match should be 5. How can I find the position of the match?

Answer 1

Exact syntax for the Postgres position() function is, quoting the manual:

position ( substring text IN string text ) → integer

With the IN keyword.

To get index support, I suggest:

SELECT *, position('ef' IN name)
FROM   tbl
WHERE  name ~ 'ef';

name ~ 'ef' od name LIKE '%ef%' can use a trigram index on (name), unlike the expression position('ef' IN name) > 0, which is not "sargable". Big difference in performance for big tables.

About that trigram index:

• PostgreSQL LIKE query performance variations

Answer 2

select name, position('ef', name)
from your_table
where position('ef', name) > 0