如果仅这些情况出现在您的数据中，则可以在下面使用查询.您可以将此逻辑放入函数中，但查询更快，更简单.

If these are the only cases that may appear in your data you could use query below. You can put this logic into function, but query is faster, simpler.

这里没什么好想的，我只是将字符串分成两半并与源进行比较.对于给定的示例有效，显然，在某些情况下，您需要更多的逻辑.例如，如果字符串中有连续的空格，则必须首先删除它们.

Nothing fancy here, I'm just dividing string into half and comparing with source. Works for given examples, obviously there may be cases where you need more logic. For instance if you have consecutive spaces in string you have to get rid of them at first.

^演示

select address, 
       case when address like sub||'%' 
            then substr(address, 1, length(address) - length(sub)) 
            else address
       end trimmed
  from (select address, trim(substr(address, instr(address, ' ', 1, sn/2 + 1))) sub 
          from (select address, regexp_count(address, ' ') sn from t))

结果:

ADDRESS                        TRIMMED
-----------------------------  -----------------------------
3 Mayers Court 3 Mayers Court  3 Mayers Court
905 Mayers Street              905 Mayers Street
Manor House Manor              Manor House
1 Briar Cottages 1 Briar       1 Briar Cottages