How to search using LIKE in Ruby

Question

I have two records in keyskill tables name "SAP FICO" and "FICO SAP". When I search the record with "FICO SAP". I want to get both records. Is there any way to fetch this record if I search?

I want this both as result if I search, I tried using like as below, but it fetches only one.

    search = "FICO SAP"
    KeySkill.where("name LIKE ?", "%#{search}%")

This is my Keyskill table data (created like this in table)

 <KeySkill id: 50, name: "SAP FICO", relevant_experience: "1", created_at: "2019-01-06 08:48:54", updated_at: "2019-01-06 08:48:54", deleted_at: nil> 
<KeySkill id: 51, name: "FICO SAP", relevant_experience: "1", created_at: "2019-01-06 08:48:55", updated_at: "2019-01-06 08:48:55", deleted_at: nil>

What database are you using? There are some full-text search gems out there that are pretty simple to use. — codenamev
– codenamev, Commented Jan 7, 2019 at 19:31

nathanvda · Accepted Answer · 2019-01-08 11:01:18Z

1

A simple way to handle this would be something like

search_term = "FICO SAP" 
terms = search_term.split 

filtered_skills = KeySkill 
terms.each do |t| 
  filtered_skills = filtered_skills.where("name like '%#{term}%'") 
end

and then just use the filtered_skills to retrieve them.

This works where each part of the original search term has to be present in a random order. So we can just use and for all terms.

[EDIT: if you want to search case insensitive]

If you want this search to be case independent, you can rewrite the where as follows if you are using postgresql

.where("name ilike '%#{term}%'")

(ilike = case Insensitive LIKE). But this method only exists for postgresql, so for any other database you would use the following:

.where("lower(name) like '%#{term.downcase}%'")

edited Jan 8, 2019 at 11:01

answered Jan 7, 2019 at 18:39

nathanvda

50k14 gold badges120 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roc Khalil Over a year ago

Better use ILIKE if you're using PostgreSQL

nathanvda Over a year ago

Good point! I extended my answer to show how to do case insensitive searching, if the OP would find that useful (actually always seems smart?).

Collectives™ on Stack Overflow

How to search using LIKE in Ruby

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related