CODEWITHSUNDEEP
ruby-on-railsregexrubypostgresql
Ben Richardson
Ben Richardson

Reputation: 37

How do I translate this PostgreSQL regular expression (case insensitive) query into an ILIKE query?

I have a query that currently uses PostgresQL...

.where("locations.address ~* '#{@street_name}.*#{@street_type}'")

In order to prevent special characters being interpreted when this lookup is performed, I need to use an ILIKE query instead.

I'm not sure what this part of the query does and therefore how to translate it

.*

Can someone help me understand how to translate this query to an ILIKE query without breaking how the current query is constructed?

Upvotes: 0

Views: 240

Answers (1)

Henry
Henry

Reputation: 15721

In regular expressions . means "any character" and * means any number of times (including zero). Therefore .* means a string containing any characters of any length.

In Postgres, the % character in a pattern (used with (I)LIKE) means "any string of zero or more characters." (docs)

So just replace the .* with % when switching from regex matching to pattern matching.

Additionally (unrelated to the actual question), it is generally a very bad idea to use string templating to create SQL queries as this method exposes you to SQL injection attacks. Instead you should use your ORM's variable functionality. For ActiveRecord, you can pass an array of values to where, where the first element is the SQL statement template and the subsequent elements are either variables to insert or a hash of named variables to insert. (docs)

For example:

.where(["locations.address ILIKE ? || '%' || ?", street_name, street_type])

or

.where(["locations.address ILIKE :street_name || '%' || :street_type", {street_name: street_name, street_type: street_type}])

Upvotes: 2

Related Questions