오라클 정규식

1 정규식 표기법[편집]

Oracle regular expression
Operator	Description
\	The backslash character can have four different meanings depending on the context. It can: Stand for itself Quote the next character Introduce an operator Do nothing
*	Matches zero or more occurrences
+	Matches one or more occurrences
?	Matches zero or one occurrence
ㅣ	Alternation operator for specifying alternative matches
^	Matches the beginning of a string by default. In multiline mode, it matches the beginning of any line anywhere within the source string.
$	Matches the end of a string by default. In multiline mode, it matches the end of any line anywhere within the source string.
.	Matches any character in the supported character set except NULL
[ ]	Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list. To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any). To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.
( )	Grouping expression, treated as a single subexpression
{m}	Matches exactly m times
{m,}	Matches at least m times
{m,n}	Matches at least m times but no more than n times
\n	The backreference expression (n is a digit between 1 and 9) matches the nth subexpression enclosed between '(' and ')' preceding the \n
[..]	Specifies one collation element, and can be a multicharacter element (for example, [.ch.] in Spanish)
[: :]	Specifies character classes (for example, [:alpha:]). It matches any character within the character class.
[==]	Specifies equivalence classes. For example, [=a=] matches all characters having base letter 'a'.

정규식 표기 설명

1) ^ : 문자열의 시작 부분과 일치. 매칭 파라미터의 m 옵션이 활성화되면 표현식을 사용하는 모든 행의 시작과 일치

$       : 문자열의 끝 부분과 일치. 매칭 파라미터의 m 옵션이 활성화되면 표현식을 사용하는 모든 행의 끝과 일치
*       : 0개 이상 (zero or more) 일치
+       : 하나 이상 (one or more) 일치
?       : 0 또는 1개 일치 (zero or one)
.       : NULL을 제외한 모든 문자와 일치
ㅣ (파이프) : OR
[ ]     : [ ]에 있는 문자 중 하나를 일치시키려는 목록을 지정
[^ ]    : [ ]에 있는 문자를 제외한 모든 문자를 일치시키려는 리스트 지정 
( )     :표현식을 하위 표현식으로 그룹화하는데 사용
{m}     :m번 일치
{m,}    :적어도 m번 일치
{m,n}   :적어도 m번 일치 ~ n번 이하 일치
\n      :() 번째 하위 표현식과 일치
[..]    :하나 이상의 문자가 될 수 있는 하나의 집합요소와 일치
[::]    :문자 클래스와 일치. [:digit:], [:punct:], [:alpha:] 등
[==]    :equivalence 클래스와 일치
\d      :숫자와 일치
\D      :숫자가 아닌 것과 일치
\w      :영문자 숫자 밑줄문자(_) 와 일치
\W      :영문자 숫자 밑줄문자(_) 이외의 문자와 일치
\s      :공백문자와 일치
\S      :공백이 아닌 문자와 일치
\A      :문자열의 시작 부분과 일치, 혹은 새 행의 문자 앞에 있는 문자열의 끝 부분과 일치
\Z      :문자열의 끝과 일치
*?      :앞선 패턴이 0번 이상 발생
+?      :앞선 패턴이 한 번 이상 발생
??      :앞선 패턴이 0 또는 1번 발생
{n}?    :앞선 패턴이 n번 일치
{n,}?   :앞선 패턴이 적어도 n번 일치

{n,m}? :앞선 패턴이 적어도 n번 ~ m번 이하 일치

2 정규식 예시[편집]

2.1 REGEXP_REPLACE[편집]

REGEXP_REPLACE

1) REGEXP_REPLACE (source_char

               , pattern
               [, replace_string ]
               [, position ]
               [, occurrence ]
               [, match_param ]

)

1) Source_char

: 원본데이터, 컬럼명, 문자열이 올수 있다.

2) pattern

: 검색 하는 패턴을 의미

3) replace_string

: 매칭된 문자열을 치환할 문자

4) position

: 검색 시작 위치, 값을 생략시 기본값 : 1

5) occurrence

: 검색패턴과 일치하는 문자열이 발생하는 횟수, 0은 모든 값을 대채, 다른 n이란 숫자를 주면 n번째 발생하는 문자열을 대체

6) match_parameter

: 상세 옵션 
  - c : 대소문자를 구분해서 검색
  - i : 대소푼자를 구분하지 않고 검색
  - m : 검색 조건을 여러 줄로 줄 수 있음

- c와 i가 중복으로 설정되면 마지막에 설정된 값을 사용 ex) ic가 중복으로 절정되면 c 옵션 적용

WITH T AS (
            SELECT '김, 수 영' NAME FROM DUAL
  UNION ALL SELECT 'KIM/SUYOUNG'  FROM DUAL            
  UNION ALL SELECT 'KIM suYOUNG'  FROM DUAL
  UNION ALL SELECT 'KIM su YOUNG'  FROM DUAL  
  UNION ALL SELECT 'KIM su,YOUNG'  FROM DUAL
  UNION ALL SELECT 'KIM,su/YOUNG'  FROM DUAL
  UNION ALL SELECT '김 수한무 거북이와 두루미 삼천갑자 동방석'  FROM DUAL               
  UNION ALL SELECT 'lee dkd dkdk dkdkd kd dkdkdkd 1 2 2'  FROM DUAL  
)
SELECT REGEXP_SUBSTR(NAME, '[^ /,]+', 1) 성
     , REGEXP_REPLACE(NAME, '([^ /,]+)([^가-힣a-zA-Z]+)([^/,-]+)', '\3\4\5') 이름     
  FROM T;

2.1.1 regexp_replace 활용[편집]

2.1.1.1 모든 숫자를 특수기호로 변경하기[편집]

SELECT text
     , regexp_replace(text
       ,'[[:digit:]]'
       , '+') "숫자->기호" 
FROM TEST

2.1.1.2 특정 패턴을 찾아서 패턴 추가하기[편집]

-- [특정 패턴을 찾아서 패턴을 추가하기]
SELECT text
     , regexp_replace(text
       , '([0-9])'
       , '\1*') "숫자->패턴" 
  FROM TEST

2.1.1.3 지역번호가 2자리이고 전화국번이 4자리인 전화번호인 학생 찾기[편집]

-- [지역번호가 2자리이고 전화국번이 4자리인 전화번호인 학생 찾기] 
-- 02)1234-4567
SELECT tel FROM student 
 WHERE regexp_replace(tel
       , '(\d{2})\)(\d{4})\-(\d{4})'
       ,'\2') > '5000'

2.1.1.4 전공이 101번인 학생의 이름 변경하기[편집]

-- [전공이 101번인 학생의 이름 변경하기]
-- 홍길동 => (홍-길-동)
SELECT name
     , RTRIM(regexp_replace(name, '(.)', '\1-'), '-') "변경후" 
  FROM student
 WHERE deptno1=101

2.1.1.5 특정 문자열을 다른 형태로 바꿀때[편집]

-- [특정 문자열을 다른 형태로 바꿀때]
SELECT REGEXP_REPLACE('20120324', 
'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',
'\1 : \2 : \3') 
FROM dual

2.1.1.6 치환할 문자를 |로 구분하여 입력[편집]

-- [치환할 문자를 |로 구분하여 입력]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs'
       , 'Stay|Foolish|Steve'
       , '')
  FROM DUAL

 --결과:  Hungry,  . -  Jobs
 ;

-- 문자를 제거만 할꺼면 3번재 파라미터는 생략가능
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs'
       , 'Stay|Foolish|Steve')
   FROM DUAL

 --결과:  Hungry,  . -  Jobs
 ;

2.1.1.7 매칭문자열 마스킹 처리 하기[편집]

-- [매칭문자열 마스킹 처리 하기]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005'
       , 'Stay|Foolish|Steve'
       , '###')
   FROM DUAL

 --결과: ### Hungry, ### ###. - ### Jobs 2005
 ;

2.1.1.8 특수문자 제거[편집]

-- [특수문자 제거]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs'
       , '[[:punct:]]')
   FROM DUAL

--결과: Stay Hungry Stay Foolish  Steve Jobs
 ;

2.1.1.9 숫자를 제외한 모든문자 제거 (숫자만)[편집]

-- [숫자를 제외한 모든문자 제거 (숫자만)]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005'
       , '[^[:digit:]]')e
   FROM DUAL
 
 --결과: 2005
 ;

 --$같은 특수문자를 치환하기 위해서는 Escape 문자(\)를 붙여 줘야함
 SELECT REGEXP_REPLACE('$Stay Hungry, Stay Foolish. - Steve Jobs'
        , '-|\$')
   FROM DUAL

 --결과: Stay Hungry, Stay Foolish.  Steve Jobs

2.1.1.10 금액 콤마 표기[편집]

SELECT REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(1234567890)), '(\d{3})','\1,')), '^,','') AS val    
  FROM dual

2.2 REGEXP_SUBSTR[편집]

REGEXP_SUBSTR

REGEXP_SUBSTR(

  COLUMN

, [REG_EXP]

, [START_INDEX]

, [GROUP_INDEX]

)

COLUMN : 컬럼명
REG_EXP : 정규표현식 작성
START_INDEX : 해당 정규표현식을 검색할 문자열의 INDEX 지정
GROUP_INDEX : 매칭되는 순번 , 해당 정규표현식으로 매칭된 문자열 그룹이 2개 이상이라면 INDEX 지정해 그룹 선택

REGEXP_SUBSTR 함수