"오라클 정규식"의 두 판 사이의 차이
DB CAFE
(→REGEXP_REPLACE) |
(→REGEXP_SUBSTR) |
||
133번째 줄: | 133번째 줄: | ||
|제목 = REGEXP_SUBSTR | |제목 = REGEXP_SUBSTR | ||
|내용 = REGEXP_SUBSTR( | |내용 = REGEXP_SUBSTR( | ||
− | + | COLUMN | |
, [REG_EXP] | , [REG_EXP] |
2023년 7월 10일 (월) 16:29 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
2 정규식 예시[편집]
2.1 REGEXP_REPLACE[편집]
- [REPLACE_STR] - replacement_string
- Optional. Matched patterns will be replaced with replacement_string in string. If the replacement_string parameter is omitted, the function simply removes all matched patterns, and returns the resulting string.
- [START_INDEX] - start_position
- Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.
- [REPLACE_INDEX] - nth_appearance
- Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string. If you specify 0 for this parameter, all appearances of pattern will be replaced in string.
- match_parameter
- Optional. It allows you to modify the matching behavior for the REGEXP_REPLACE function.
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;
--치환할 문자를 |로 구분하여 입력
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:]]')
FROM DUAL
--결과: 2005
;
--$같은 특수문자를 치환하기 위해서는 Escape 문자(\)를 붙여 줘야함
SELECT REGEXP_REPLACE('$Stay Hungry, Stay Foolish. - Steve Jobs', '-|\$')
FROM DUAL
--결과: Stay Hungry, Stay Foolish. Steve Jobs
2.2 REGEXP_SUBSTR[편집]
notifications_active REGEXP_SUBSTR 함수
REGEXP_SUBSTR(
문자(컬럼명)
, 정규식 패턴
, 시작 하는 위치(최소값1)
, 매칭 되는 순번
)
- 간단 예제
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
2.3 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')