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 using the PostgreSQL database in my project. I currently have a list of hospitals with a name like:

Hospital A Hospital C Hospital D Hospital BA 

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

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

 Hospital A Hospital BA 

I did a search on this question and found this answer: Advanced MySQL Alphabetical Sort with Prefix? but the answer is using a view that I don’t know how to do it in Rails.

I'm not sure if the pg_search gem supports SUBSTRING since I did not find a document about this in my github. Does anyone know if Postgres has the ability to do this kind of work or not?

Thanks for any help.

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

Answer 1

You can disable the prefix. Shown here with the prefix "Hospital". Since it doesn't seem to you that the space after the prefix I added 1 to skip it. Of course, you can also just enter a number if it is always the same. To split everything up to the first space, you can use a regular expression. The trick is to create a temporary table into which you add everything you need, and then you easily select it. If the prefix changes, you can limit the internal selection to WHERE 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 script