Find strings in PostgreSQL and order by distance to beginning of string

Question

I am trying to write a query that looks up a partial string, but order by proximity to the front of the string. It's for a typeahead application.

Here's my query currently:

SELECT  DISTINCT ON (full_name) full_name
FROM users
WHERE (lower(full_name) LIKE 'q%' OR lower(full_name) LIKE '%q%')
LIMIT 10

But it does not seem to order in the way I would expect.

So if I search for 'pet' I would like to return peter smith and petra glif before abigail peters.

Is it possible to write that where clause in this way? We don't currently have any fuzzy text search modules installed in the database so I would like to avoid doing that if possible.

Answer 1

You can use position(substring in string) function for this:

order by position(lower('pet') in lower(full_name))

http://www.postgresql.org/docs/9.1/static/functions-string.html