오라클 정규식 치환 regexp_replace

REGEXP_LIKE
REGEXP_INSTR
REGEXP_SUBSTR 등이 존재하니 참고하시기 바랍니다.

http://www.psoug.org/reference/regexp.html

http://www.adminschool.net/wiki/doku.php?id=dbms:oracle:devel:regexp

 

 

 

정규식 패턴으로 java ,visual basic ,java script ,펄,....등 동일한  표현 방법으로 os언어에 무관하게

적용할수있다

 

reqexp_like 함수

SQL> select title from wowbbs where rownum <10;

 

TITLE

----------------------------------------------------------

 

444444444444

사용자 계정 관리

Pass word 관리 프로파일 관리

Object Privileges(권한)

System Privileges

Table Space 개념 및 생성 관리

index 종류 원리

index가 적용되지 않는경우

Descending  Index (내림순서 인덱스)

 

9 개의 행이 선택되었습니다.

 

SQL> select title from wowbbs where REGEXP_LIKE(title, '^[[:digit:]]')

  2  ;

 

TITLE

-----------------------------------------------------------------------------

 

444444444444

 

SQL>

 

^[[:digit:]] 숫자로 시작하는  패턴 검색

 

 

 

REGEXP_INSTR:임의의 문자열에서 주워진 패턴이 존제하는 위치를 출력한다(oracle 참조)

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',

       '[[:digit:]]{5}$')

       AS rx_instr

  FROM dual

  RX_INSTR

----------

        45

 

REGEXP_REPLACE  :

 

SELECT REGEXP_REPLACE(

       'Ellen Hildi Smith',

       '(.*) (.*) (.*)', '3, 1 2')

FROM dual

 

SELECT REGEXP_SUBSTR(

       'The final test is is the implementation',

       '([[:alnum:]]+)([[:space:]]+)') AS substr

  FROM dual

SUBSTR

------

is is

 

Oracle 에서 check 조건

 

check 조건 설정 방법

ALTER TABLE students    ADD CONSTRAINT stud_ssn_ck CHECK    (REGEXP_LIKE(ssn,    '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 

 

Java Script 적용 예)

 *  0번이상
 + 1번이상
 ?  0번 이나  1 번
 . 그자리에 아무문자 
 [] 한문자 범위 0-9, a-zA-Z, - [a-zA-Z-]
 {} 반복
 () 부분식
 ^ 시작할때 , [^] 부정 [^0-9] 숫자가 아닌것
 $ 끝날때
 | 또는 malja | sunja   = 말자나 순자

 [0-9] : 숫자
 [9-0] : 9 또는 - 또는 0
 축약
 [0-9] => \d   ,  
 [^0-9] => \D  ,
 [a-zA-Z0-9_] => \w ,
 [^a-zA-Z0-9_] => \W

 

Oracle 적용 예)

SELECT  /*+ no_merge parallel(a 4) parallel(c 4) */

        c.intg_cust_no                            cust_no

--  ----------------------------------------------------------------------------

--  [2008.10.11]수정처리부분

--  수정자 : ***

--  수정조건

--  1) 숫자 이외의 데이터는 제외

--  2) len(앞_주민 + 뒤_주민) in (10, 13) 이외의 데이터는 NULL

--  3) 앞-5 + 뒤-5 / 앞-6 + 뒤-7로 데이터 분리

--  ----------------------------------------------------------------------------

--        , DECODE(LENGTH(a.jumin_no), 10, SUBSTR(a.jumin_no, 1, 5), SUBSTR(a.jumin_no, 1, 6))

--                                                head_rs_bz_no

--        , DECODE(LENGTH(a.jumin_no), 10, SUBSTR(a.jumin_no, 6, 5), SUBSTR(a.jumin_no, 7, 7))

--                                                tail_rs_bz_no

--        , DECODE(LENGTH(a.jumin_no), 13, '01', 10, '02')

--                                                cust_div_cd

--------------------------------------------------------------------------------

    , CASE  WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=10

            THEN  SUBSTR(REGEXP_REPLACE(a.jumin_no,'\D',''),1,5)

            WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=13

            THEN  SUBSTR(REGEXP_REPLACE(a.jumin_no,'\D',''),1,6)

            ELSE  NULL

      END                                         head_rs_bz_no

    , CASE  WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=10

            THEN  SUBSTR(REGEXP_REPLACE(a.jumin_no,'\D',''),6,5)

            WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=13

            THEN  SUBSTR(REGEXP_REPLACE(a.jumin_no,'\D',''),7,7)

            ELSE  NULL

      END                                         tail_rs_bz_no

    , CASE  WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=10

            THEN  '02'

            WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))=13

            THEN  '01'

      END                                         cust_div_cd

--  ----------------------------------------------------------------------------

--  [2008.10.11]수정추가처리부분

--  수정자 : ***

--  수정조건

--  1) 주민번호가 유효하지 않은 경우 : '02'

--  ----------------------------------------------------------------------------

    , CASE  WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))!=10

              OR  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))!=13

            THEN  '02'

      END                                         cust_st_cd

--  ----------------------------------------------------------------------------

--  수정조건

--  1) '주민사업자번호 오류에의한 자료보정:' + 앞_주민 + 뒤_주민

--  ----------------------------------------------------------------------------

    , CASE  WHEN  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))!=10

              OR  LENGTH(REGEXP_REPLACE(a.jumin_no,'\D',''))!=13

            THEN  '주민사업자번호 오류에의한 자료보정:'||a.jumin_no

      END                                         rmk

      --  ----------------------------------------------------------------------------

--------------------------------------------------------------------------------

      , CASE  WHEN SUBSTR(a.jumin_no, 7, 1) IN ('1', '3', '5', '7', '9') THEN '1'

              WHEN SUBSTR(a.jumin_no, 7, 1) IN ('2', '4', '6', '8', '0') THEN '2'

        END                                       sex_cd

      , a.name                                    cust_nm

      , a.email                                   eml_addr

      , a.int_yn                                  grp_agr_yn

      , TO_TIMESTAMP(TO_CHAR(a.regdate, 'yyyymmdd'), 'yyyy-mm-dd hh24:mi:ssxff')

                                                  inpt_dtime

      , SUBSTR(a.jumin_no, 3, 4)                  use_pw

FROM    mem_b_info_c                              a

      , work_cust_01                              c

WHERE   c.site_cust_no                            = a.mem_b_id

AND     c.site_code                               = '03'

AND     c.rpt_yn                                  = 'N'

 

 

 

========================================================================================================================

========================================================================================================================

 

 

 

봉천1동 => 봉천동
봉천2동 => 봉천동
봉천10동 => 봉천동

즉 모든 숫자들을 널문자로 치환하면 되는 것이다.

해답)

오라클에서 정규식이 지원되지 않던 이전 버젼에서는 다음과 같이 한다.

SQL> select REPLACE(TRANSLATE('봉천12동', '1234567890', '**********'), '*', '') from dual;REPLAC
------
봉천동1 rows selected.

translate 함수를 사용하여 숫자들을 모두 '*' 등 사용하지 않는 문자로 먼저 변환하고,
최종적으로 replace 함수를 사용하여 널문자로 치환하는 것이다.

정규식이 지원된다면 다음과 같이 간단하게 해결한다.

SQL> select REGEXP_REPLACE('봉천12동', '[0-9]', '') from dual;REGEXP
------
봉천동1 rows selected

출처 : http://blog.naver.com/tyboss/70042529444

] 오라클 정규식 치환 regexp_replace|작성자 마루아라