CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
RedZagogulin
RedZagogulin

Reputation: 461

Normalize seeking value in SQL search query

[PostgreSQL(9.4), Rails(4.1)]

The problem:
I have a table with the names of tools. The column_name is hstore type and looks like this: name -> ('en': value, 'de': value). Worth noting that 'de' is unnecessary in this problem, cause all names are stored only in 'en' key.
Next I have to construct a search query that will find the right record, but the format of the text in query are unknown, e.g.:

In DB:
WQXZ 123GT, should match query: WQXZ_123-GT

In DB:
Three Words Name 123-D45, should match query: Three_WORDS_NAME 123D45

and so on...

Solution:
To get this happen I want to normalize the value that I'm looking for and the query in such way that both of them will be identical. To do this I need to make both values in downcase, remove all whitspaces, remove all non-alphanumeric characters, so the values above will be:
wqxz123gt == wqxz123gt and threewordsname123d45 == threewordsname123d45

I have no problem to format a search value in ruby: "sTR-in.g24 3".downcase.gsub(/\s/, "").gsub(/\W/, "") # => "string243" But I can't understand how to do this in SQL-search query to look like:

Tool.where("CODE_I_AM_LOOKING_FOR(name -> 'en') = (?)", value.downcase.gsub(/\s/, "").gsub(/\W/, ""))

Thank you for your time.

UPD: I can make a downcase in query:

Tool.where("lower(name -> 'en') = (?)", value.downcase)

But it solves only a part of the problem (downcase). The whitespaces and non-word characters (dots, dashes, underscores, etc.) are still an issue.

Upvotes: 0

Views: 148

Answers (2)

RedZagogulin
RedZagogulin

Reputation: 461

Nitin Srivastava's answer directed me in right direction. All I needed was to use regexp_replace function.

So the proper query is:

Tool.where(
  "lower(regexp_replace((name -> 'en'), '[^a-zA-Z0-9]+', '', 'g')) = ?",
  value.downcase.gsub(/\s/, "").gsub(/\W/,"")
)

Upvotes: 0

Nitin Srivastava
Nitin Srivastava

Reputation: 1424

You can use Postgres replace function to remove spaces. Then use lower function to match on that value. Like this.

Tool.where("lower(replace(name -> 'en', ' ', '')) = (?)", value.downcase.gsub(/\s/, "").gsub(/\W/, "") )

I hope this would be helpful.

Upvotes: 1

Related Questions