0

I'd like to query rows from database that might start with any word from an alternation list. For instance, querying the name blue tree house would return records such as the blue tree house or le blue tree house or A blue tree house.

I attempted querying as follows:

SELECT NAME
FROM NAMES
WHERE (lower(names.name) ~ '^(no.?)*\s*\d*\s*\W*(an|the|le|leur|ils|a)?(blue)\W*\s*\y') AND
      (lower(names.name) ~ ' \yhouse ?\y')

Here the Fiddle with additional examples and setup.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
John Barton
  • 1,581
  • 4
  • 25
  • 51

1 Answers1

1

Since the end of the string is invariable in each search pattern, I suggest to work with reversed names, so you can use a matching index efficiently:

CREATE INDEX ON names (lower(reverse(name)) COLLATE "C");

You may want to disallow trailing (and leading) spaces in name.

Then:

SELECT name
FROM   names
WHERE  lower(reverse(name)) LIKE lower(reverse('BLUE HOUSE')) || '%' -- backed by index
AND    name ~* '^((?:no\.?\s*)?\d*\y| a|an|the|le|leur|ils|a)\y';  -- filter the few remaining rows

db<>fiddle here

See:

Is your "alternation list" complete? I added an expression to cover all examples you provided.

Noteworthy: (?:no\.?\s*)?

(?:) ... capturing parentheses
\. ... literal dot needs escaping backslash
\s ... whitespace character
? ... 0 or 1 times (like you had it, not required by examples)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228