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');
insert into names values(2,'LE BLUE HOUSE');
insert into names values (3,'A BLUE HOUSE');
insert into names values (4,'no. 20 THE BLUE HOUSE');
insert into names values (5,'20 -- BLUE HOUSE');
insert into names values (6,' A BLUE HOUSE');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

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 version();


version


PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit