Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE names(
  id serial PRIMARY KEY
, name VARCHAR(50) UNIQUE NOT NULL
);

INSERT INTO names VALUES
  (1, 'THE BLUE HOUSE')
, (2, 'LE BLUE HOUSE')
, (3, 'A BLUE HOUSE')
, (4, 'no. 20 THE BLUE HOUSE')
, (5, '20 -- BLUE HOUSE')
, (6, ' A BLUE HOUSE');

CREATE INDEX ON names (lower(reverse(name)));

CREATE TABLE

INSERT 0 6

CREATE INDEX

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')

name

20 -- BLUE HOUSE

SELECT 1

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

name

THE BLUE HOUSE

LE BLUE HOUSE

A BLUE HOUSE

no. 20 THE BLUE HOUSE

20 -- BLUE HOUSE

 A BLUE HOUSE

SELECT 6