CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Cat Named Dog
Cat Named Dog

Reputation: 1698

Search similar address with SQL query

I need find addresses that are similar using an SQL query. I'm using Ruby on Rails.

This is the query I'm using:

properties.where('lower(street_address) LIKE ?', "%#{params[:address].downcase}%")

I have two properties with the following addresses:

123 Happy st 123 East Happy st

When I do a search (which is params[:address]) for 123 happy st, i only get the first value.

If I do east happy st, i get only one

How can i formulate a query to get both records?

Upvotes: 1

Views: 134

Answers (1)

Sebastián Palma
Sebastián Palma

Reputation: 33470

You can do a case insensitive filter using a regular expression:

SELECT "properties".* FROM "properties" WHERE (street_address ~* '123|happy|st')

For that you need to split the params[:address] and join them with a pipe "|":

Property.where("street_address ~* ?", "123 happy st".split.join('|'))

Note, this matches both examples, but being a regular expression it could match anything else.

Upvotes: 1

Related Questions