CODEWITHSUNDEEP
postgresql
JHo
JHo

Reputation: 1088

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

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.

Upvotes: 5

Views: 2574

Answers (2)

klin
klin

Reputation: 121604

You can use boolean expression full_name ILIKE 'q%' as a sort order.

SELECT * 
FROM (
    SELECT DISTINCT full_name
    FROM users
    WHERE full_name ILIKE '%q%'
    ) alias
ORDER BY full_name ILIKE 'q%' DESC, full_name
LIMIT 10

Note that there is ILIKE operator for case-insensitive LIKE.

You may be also interested in Full Text Search.

Upvotes: 5

David Aldridge
David Aldridge

Reputation: 52346

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

Upvotes: 6

Related Questions