WITH TAB

AS

(

SELECT '경기도|용인시|처인구' ADDR FROM DUAL UNION ALL

SELECT '경기도|용인시|처인구' FROM DUAL UNION ALL

SELECT '경기도|용인시|처인구' FROM DUAL UNION ALL

SELECT '경기도|용인시|처인구' FROM DUAL UNION ALL

SELECT '경기도|용인시|기흥' FROM DUAL

)

SELECT ADDR

, REGEXP_SUBSTR(ADDR,'[^|]*') AA1

, REGEXP_REPLACE(ADDR, '(.*)[|](.*)[|](.*)', '\1') AS AA2_1

, REGEXP_REPLACE(ADDR, '(.*)[|](.*)[|](.*)', '\2') AS AA2_2

, REGEXP_REPLACE(ADDR, '(.*)[|](.*)[|](.*)', '\3') AS AA2_3

, REGEXP_SUBSTR(ADDR,'[^|]*$') AA3

FROM TAB

'DB/ETL > Oracle' 카테고리의 다른 글

[DB - Oracle] TableSpace 생성  (0) 2010.11.10
[Oracle] 동적쿼리를 받아서 커서 처리  (0) 2010.10.04
[ORACLE] REGEXP_SUBSTR, REGEXP_REPLACE  (0) 2010.09.28
[Oracle] Function, Procedure Grant...  (0) 2010.09.11
[Oracle] Procedure  (0) 2010.08.11
[Oracle] Temporary Table 생성  (0) 2010.07.30
Posted by pino93