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

Question

I am using Ruby on Rails and pg_search gem for full-text search function using PostgreSQL database 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.

Updated: I ended up use a column to store this prefix word so that I can search the content and exclude that word from query and return expected results. For anyone who are facing this problem, please consider and try my solution if it can save you.

Answer 1

You can cut off the prefix. Shown here with prefix 'Hospital'. Because you don't seem to want the space after the prefix I added 1 to skip it. Of course you can also just put in the number if it is always the same. To split of everything until the first space you can use regex. The trick is to create a temporary table where you add everything like you need it and then you comfortably select from it. If the prefix is changing you might want to restrict the inner select to WHERE name LIKE 'Hospital%'.

SELECT
   name
FROM (
   SELECT
      SUBSTR(name, LENGTH('Hospital') + 1) AS name
   FROM
      hospital
   ) AS T
WHERE
   name LIKE '%A%'
ORDER BY
   name
;

SQL fiddle