CODEWITHSUNDEEP
ruby-on-railsregexpostgresqlsearch
Dan
Dan

Reputation: 1246

Postgres search for

If provided with a text string e.g. "Tennis Account A33 Sydney AU" I need to find a db record that matches it. The "A33" part could be any characters.

I've created a model for my rails app called Descriptor which has the string column called pattern. The pattern can include the % character to show that any characters could be in this place in the string. e.g. "Tennis Account % Sydney AU"

So if the data in the table was as below and I wanted to find the descriptor which matched "Tennis Account A33 Sydney AU" it should return descriptor 1.

#<Descriptor id: 1, pattern: "Tennis Account % Sydney AU", ... >
#<Descriptor id: 2, pattern: "Tennis Account % Melbourne AU", ... >
#<Descriptor id: 3, pattern: "Tennis Account Sydney AU", ... >
#<Descriptor id: 4, pattern: "Jazz Flute Minions dney AU", ... >

......thousands more descriptors

How can this search query be written in postgres?

Upvotes: 0

Views: 30

Answers (1)

Dan
Dan

Reputation: 1246

I've now realised this is just a LIKE query around the other way:

Descriptor.where('? LIKE pattern', "Tennis Account A33 Sydney AU").first

Upvotes: 1

Related Questions