mardi 16 juin 2015

how to search and sort data but exclude prefix words in sql

I am using pg_search for full-text search function using PostgreSQL in my project. Currently, I have a list hospitals with name like:

Hospital A
Hospital C
Hospital D
Hospital B A

When I make search, I would like the word Hospital does not affect to returned results and the results can be sorted without Hospital prefix.

Example if I enter Hospital in query field, there will have no returned results, but if I enter A, Hospital A and Hospital B A will be results, then the results can be sorted by alphabetical without Hospital prefix word to become a list:

Hospital A
Hospital B A

I made a search about this and found this answer: Advanced MySQL Alphabetical Sort with Prefix?, but the answer using a view which I don't know how to do it in Rails.

I don't sure if pg_search gem supports SUBSTRING as I have found no document about it on its github. Does anyone know if Postgres has features to do this kind of work or not?

Thanks for any help.

Aucun commentaire:

Enregistrer un commentaire