주소는 한번에 처리하기 힘들 수 있습니다. 다소 복잡한 정규표현식을 몇번에 걸쳐야 요소들을 추출할 수 있습니다. 그리고 여러가지 예외사항들이 존재할 수 있다는 점 참고하세요.

아래 쿼리는 참고만 하시고, 커스터마이징 하시길 바랍니다.

WITH T AS (

SELECT '서울 강남구 대치동 111-11  OO빌라 2층'  ADDR FROM DUAL UNION ALL

SELECT '서울 강남구 대치2동 999  XXX수퍼1층 '   ADDR FROM DUAL UNION ALL

SELECT '서울 강남구 대치동 888번지'  ADDR FROM DUAL

)

,t1 as

(

-- 동이나 리이후 숫자후 공백

select addr,trim(regexp_replace(regexp_substr(addr,'[동|리][ ]*[0-9]+[ ]+'),'[동|리]')) as bunji

  from t

union all

-- 동이나 리이후 숫자-숫자후 공백

select addr,trim(regexp_replace(regexp_substr(addr,'[동|리][ ]*[0-9]+-[0-9]+[ ]+'),'[동|리]'))

  from t 

union all

-- 동이나 리이후 숫자후 번지

select addr,trim(regexp_replace(regexp_substr(addr,'[동|리][ ]*[0-9]+번지'),'[동|리|번지]'))

  from t

union all

-- 동이나 리이후 숫자-숫자후 번지

select addr,trim(regexp_replace(regexp_substr(addr,'[동|리][ ]*[0-9]+-[0-9]+번지'),'[동|리|번지]'))

  from t 

)

select *

  from t1

WITH t AS
(
SELECT '서울 강남구 대치동 111-11  OO빌라 2층' addr FROM dual
UNION ALL SELECT '서울 강남구 대치2동 999  XXX수퍼1층 ' FROM dual
UNION ALL SELECT '서울 강남구 대치동 888번지' FROM dual
UNION ALL SELECT '서울 강남구 대치동' FROM dual
UNION ALL SELECT '서울특별시 종로구 종로 19 르미에르' FROM dual
UNION ALL SELECT '경기도 이천시 마장면 양촌리 산19-1' FROM dual
UNION ALL SELECT '경기도 이천시 마장면' FROM dual
)
SELECT addr
     , REGEXP_SUBSTR(addr, '.+[읍면동가로리]( 산?[0-9][^ ]+)?') x
  FROM t
;

 황의중님, 마농님 감사합니다~~