用正则非常方便：

odb=> 
odb=> with qa as(select unnest('{1栋101室,20栋202室,恒大名都1栋101室,1栋1单元101室}'::varchar[]) as name)
odb-> select substring(name,'(\d+栋)')||substring(name,'(\d+室)') from qa;
 ?column?  
-----------
 1栋101室
 20栋202室
 1栋101室
 1栋101室
(4 rows)

odb=> 

SQL> with t as 
  2  (
  3  select '1栋101室' as name from dual
  4  union all
  5  select '20栋202室' from dual 
  6  union all
  7  select '恒大名都1栋101室' from dual
  8  union all
  9  select '1栋1单元101室' from dual
 10  )
 11  select * from t
 12  where regexp_like(name,'^[[:digit:]]+栋+[[:digit:]]+室$');

NAME
------------------------------------------------------------------
1栋101室
20栋202室

没有写哪种数据库和相关版本哦，不同数据库SQL的写法有差异