REGEXP_REPLACE 함수
문자열에 존재하는 임의의 패턴을 대치하여 출력한다. 
【형식】 
     REGEXP_REPLACE (source_char, pattern 
                      [, replace_string
                      [, position 
                      [, occurrence 
                      [, match_param]]]]
                    )




source_char
  찾을 문자로 데이터타입은 :CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB임

pattern
  512바이트까지의 정규표현식으로 데이터타입이 CHAR, VARCHAR2, NCHAR, NVARCHAR2

replace_string
  데이터타이브 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB로 

     CLOB와 NCLOB는 32K, 대치문자는 500 backreferences까지 

position
  찾을 문자의 위치로 디폴트는 1이며, 이는 첫 글자부터 찾는 다는 의미

occurrence
  패턴과 일치가 발생하는 횟수

      0: occurrence에서 매칭되는 모두를 대치

      n: n번째 occurrence에서 매칭되는 것을 대치

match_parameter
  디폴트 매칭을 변경할 때 사용




【예제】
SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- ------------------------------
         1 arirnag              http://arirang.co.kr
         2 seoul                http://seoul.co.kr
         3 home                 http://home.co.kr

SQL> select unique regexp_replace(email,'http://([^/]+).*', '\1')
  2  from test;

REGEXP_REPLACE(EMAIL,'HTTP://([^/]+).*','\1')
----------------------------------------------------
arirang.co.kr
home.co.kr
seoul.co.kr

SQL> 
【예제】
SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- ------------------------------
         1 arirang              arirang@abc.co.kr

SQL> select email, regexp_replace(email, 'arirang','seoul') from test;

EMAIL                  REGEXP_REPLACE(EMAIL,'ARIRANG','SEOUL')
---------------------- ---------------------------------------
arirang@abc.co.kr      seoul@abc.co.kr


SQL>
【예제】
SQL> select regexp_replace ('Kim Loves Cho','(.*) (.*) (.*)', '\3 \2 \1')   
  2  from dual;

REGEXP_REPLAC
-------------
Cho Loves Kim

SQL> 


SQL> show user
USER is "OE"
SQL>
SQL> select REGEXP_REPLACE(phone_number,
  2       '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
  3       '(\1) \2-\3') "REGEXP_REPLACE"
  4  from employees
  5  order by "REGEXP_REPLACE";

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4444
(515) 123-4567
(515) 123-4568
(515) 123-4569
(515) 123-5555
(515) 123-7777
(515) 123-8080
(515) 123-8181
(515) 123-8888
(515) 124-4169
(515) 124-4269
(515) 124-4369
(515) 124-4469
(515) 124-4567
(515) 124-4569
(515) 127-4561
(515) 127-4562
(515) 127-4563
(515) 127-4564
(515) 127-4565
(515) 127-4566
(590) 423-4560
(590) 423-4567
(590) 423-4568
(590) 423-4569
(590) 423-5567
(603) 123-6666

REGEXP_REPLACE
--------------------------------------------------------------------------------
(650) 121-1234
(650) 121-1834
(650) 121-2004
(650) 121-2019
(650) 121-2034
(650) 121-2874
(650) 121-2994
(650) 121-8009
(650) 123-1234
(650) 123-2234
(650) 123-3234
(650) 123-4234
(650) 123-5234
(650) 124-1214
(650) 124-1224
(650) 124-1334
(650) 124-1434
(650) 124-5234
(650) 124-6234
(650) 124-7234
(650) 124-8234
(650) 127-1634
(650) 127-1734
(650) 127-1834
(650) 127-1934
(650) 501-1876
(650) 501-2876

REGEXP_REPLACE
--------------------------------------------------------------------------------
(650) 501-3876
(650) 501-4876
(650) 505-1876
(650) 505-2876
(650) 505-3876
(650) 505-4876
(650) 507-9811
(650) 507-9822
(650) 507-9833
(650) 507-9844
(650) 507-9876
(650) 507-9877
(650) 507-9878
(650) 507-9879
(650) 509-1876
(650) 509-2876
(650) 509-3876
(650) 509-4876
011.44.1343.329268
011.44.1343.529268
011.44.1343.629268
011.44.1343.729268
011.44.1343.829268
011.44.1343.929268
011.44.1344.129268
011.44.1344.345268
011.44.1344.429018

REGEXP_REPLACE
--------------------------------------------------------------------------------
011.44.1344.429268
011.44.1344.429278
011.44.1344.467268
011.44.1344.478968
011.44.1344.486508
011.44.1344.498718
011.44.1344.619268
011.44.1344.987668
011.44.1345.429268
011.44.1345.529268
011.44.1345.629268
011.44.1345.729268
011.44.1345.829268
011.44.1345.929268
011.44.1346.129268
011.44.1346.229268
011.44.1346.329268
011.44.1346.529268
011.44.1346.629268
011.44.1346.729268
011.44.1644.429262
011.44.1644.429263
011.44.1644.429264
011.44.1644.429265
011.44.1644.429266
011.44.1644.429267
555-1212

108 rows selected.

SQL> select REGEXP_REPLACE(country_name, '(.)','\1 ') "REGEXP_REPLACE"
  2  from countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
S w i t z e r l a n d
C h i n a
G e r m a n y
D e n m a r k
E g y p t
F r a n c e
H o n g K o n g
I s r a e l
I n d i a
I t a l y
J a p a n
K u w a i t
M e x i c o
N i g e r i a
N e t h e r l a n d s
S i n g a p o r e
U n i t e d   K i n g d o m
U n i t e d   S t a t e s   o f   A m e r i c a
Z a m b i a
Z i m b a b w e

25 rows selected.

SQL> select REGEXP_REPLACE('500  Oracle Parkway, Redwood Shores, CA',
  2                         '( ){2,}', ' ') "REGEXP_REPLACE"
  3  from dual;

REGEXP_REPLACE
----------------------------------------------------------------------------
500 Oracle Parkway, Redwood Shores, CA

SQL>