다른 명령
정규식 표기법
| Operator | Description |
|---|---|
| \ |
The backslash character can have four different meanings depending on the context. It can:
|
| * |
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'. |
정규식 예시
REGEXP_REPLACE
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;
regexp_replace 활용
모든 숫자를 특수기호로 변경하기
SELECT text
, regexp_replace(text
,'[[:digit:]]'
, '+') "숫자->기호"
FROM TEST
특정 패턴을 찾아서 패턴 추가하기
-- [특정 패턴을 찾아서 패턴을 추가하기]
SELECT text
, regexp_replace(text
, '([0-9])'
, '\1*') "숫자->패턴"
FROM TEST
지역번호가 2자리이고 전화국번이 4자리인 전화번호인 학생 찾기
-- [지역번호가 2자리이고 전화국번이 4자리인 전화번호인 학생 찾기]
-- 02)1234-4567
SELECT tel FROM student
WHERE regexp_replace(tel
, '(\d{2})\)(\d{4})\-(\d{4})'
,'\2') > '5000'
전공이 101번인 학생의 이름 변경하기
-- [전공이 101번인 학생의 이름 변경하기]
-- 홍길동 => (홍-길-동)
SELECT name
, RTRIM(regexp_replace(name, '(.)', '\1-'), '-') "변경후"
FROM student
WHERE deptno1=101
특정 문자열을 다른 형태로 바꿀때
-- [특정 문자열을 다른 형태로 바꿀때]
SELECT REGEXP_REPLACE('20120324',
'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',
'\1 : \2 : \3')
FROM dual
치환할 문자를 |로 구분하여 입력
-- [치환할 문자를 |로 구분하여 입력]
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
;
매칭문자열 마스킹 처리 하기
-- [매칭문자열 마스킹 처리 하기]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005'
, 'Stay|Foolish|Steve'
, '###')
FROM DUAL
--결과: ### Hungry, ### ###. - ### Jobs 2005
;
특수문자 제거
-- [특수문자 제거]
SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs'
, '[[:punct:]]')
FROM DUAL
--결과: Stay Hungry Stay Foolish Steve Jobs
;
숫자를 제외한 모든문자 제거 (숫자만)
-- [숫자를 제외한 모든문자 제거 (숫자만)]
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
금액 콤마 표기
SELECT REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(1234567890)), '(\d{3})','\1,')), '^,','') AS val
FROM dual
REGEXP_SUBSTR
- 간단 예제
REGEXP_SUBSTR('C-01-02','[^-]+',1,1)
결과 = C
REGEXP_SUBSTR('C-01-02','[^-]+',1,2)
결과 = 01
REGEXP_SUBSTR('C-01-02','[^-]+',1,3)
결과 = 02
— |으로 구분자 나누기 — regexp_substr(A.TXT,’[^_]+’,1,1) 1번째 SELECT distinct regexp_substr(A.TXT, '[^|]+', 1, LEVEL) TXT FROM (SELECT 'A|B|C|D' TXT FROM dual) A CONNECT BY LEVEL <= length(regexp_replace(A.TXT, '[^|]+',''))+1 ;
SELECT REGEXP_SUBSTR('test@domain.com', '[^@]+', 1, 1) AS EMAIL_ID
, REGEXP_SUBSTR('test@domain.com', '[^@]+', 1, 2) AS EMAIL_DOMAIN
FROM REG_EXP_TEST
- COLUMN TO ROW (컬럼 => 로우)
SELECT TRIM(REGEXP_SUBSTR('02,031,032','[^,]+',1,LEVEL)) AS LIST_TO_ROW
FROM DUAL
CONNECT BY INSTR('02,031,032', ',', 1, LEVEL-1 ) > 0
REGEXP_LIKE
WITH TEMP_TABLE AS ( SELECT 'Samsung Galaxy Note 4' TEXT FROM dual union all SELECT 'Apple iPhone 6 Plus' TEXT FROM dual union all SELECT 'Samsung Galaxy S5' TEXT FROM dual union all SELECT 'Apple iPhone 6' TEXT FROM dual union all SELECT 'LG G3' TEXT FROM dual union all SELECT 'SonyXperia Z3' TEXT FROM dual union all SELECT 'Motorola Moto G' TEXT FROM dual union all SELECT 'HTC One M8' TEXT FROM dual union all SELECT 'Nokia Lumia 930' TEXT FROM dual ) SELECT * FROM TEMP_TABLE WHERE REGEXP_LIKE(TEXT, 'Samsung|Apple|Nokia')
- 다건 검사
REGEXP_LIKE(TOBE_COL_NM, '_AMT|_AMT2|_CPM|_PNT|_QTY|_RT|_VAT')