------------------------------------------------
-- 정규식(REGULAR EXPRESSION) 함수
------------------------------------------------
-- 일반적인 정규식 테이블 확인
SELECT * FROM T_REG;
-- (1) REGEXP_LIKE 함수 : LIKE 함수처럼 특정 패턴과 매칭되는 결과를 검색해내는 함수
-- 영문자가 들어가 있는 행만 출력하기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Za-z]');
-- 소문자로 시작하고 공백을 포함하는 경우
-- 소문자로 시작하고 뒤에 공백이 있는 모든 행을 찾기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z] ');
-- 소문자로 시작하고 공백이 1칸 있고 숫자로 끝나는 행
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z] [0-9]');
-- 공백이 있는 모든 데이터 찾기
-- [:문자클래스:] - alpha, blank, cntrl, digit, graph, lower, print, space, upper, xdigit
-- [:digit:] : [0-9]의미, [:alpha:] : [A-Za-z]의미
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:space:]]');
-- 연속적인 글자수 지정하기
-- 대문자가 연속적으로 2자이상 나오는 경우 찾기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]{2}');
-- 숫자가 연속적으로 3자 이상 나오는 경우
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[0-9]{3}');
-- 문자와 숫자가 연속적으로 3자 이상 나오는 경우
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z][0-9]{3}');
-- 대문자가 들어가는 모든행 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:upper:]]'); -- [A-Z]와 유사
-- 특정 위치를 지정하여 출력하기
-- 시작되는 문자 지정에는 ^
-- 끝나는 문자 지정에는 $
-- 첫시작을 대문자나 소문자로 시작
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[A-Za-z]');
-- 첫시작을 숫자로 시작
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[0-9]');
-- 소문자로 시작하거나 숫자로 시작 - |은 선택
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[a-z]|^[0-9]');
-- student 테이블에서 학생의 id 중에서 첫 글자가 M 으로 시작하고
-- 두 번째 글자가 a 나 o 가 오는 id 를 이름과 함께 출력하기
SELECT NAME, ID
FROM STUDENT
WHERE REGEXP_LIKE(ID, '^M[a|o]');
-- $ 문자를 사용하여 소문자로 끝나는 행을 출력하기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z]$'); -- '[[:alpha:]]$'
-- 위와 동일한 결과
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:alpha:]]$');
-- 소문자로 시작하지 않는 행
-- 대괄호 안의 문자가 아닌 다른 것만 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^a-z]');
-- 숫자로 시작하지 않는 행
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^0-9]');
-- 소문자나 숫자로 시작하지 않는 행을 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^a-z0-9]');
-- 위치에 상관없이 소문자로만(모두 소문자) 구성된 행 제거하기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[^a-z]');
-- 소문자가 들어간 모든 행 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z]');
-- 소문자가 들어간 모든 행 제거 (NOT 사용)
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[a-z]');
-- student 테이블에서 지역번호가 2 자리이고 그 다음 국번이 연속적으로 4 자리가 나오는 값을 출력하기
SELECT NAME, TEL
FROM STUDENT
WHERE REGEXP_LIKE(TEL, '[0-9]{2}\)[0-9]{4}');
-- student 테이블에서 학생의 id 에서 4번째 자리에 r(소문자) 이 있는 행을 출력
SELECT NAME, ID
FROM STUDENT
WHERE REGEXP_LIKE(ID, '^...r'); -- 그냥 ...r 경우와 비교!!
-- t_reg2 테이블에서 ip 주소가 10.10.10.1 인 행만 출력하기
SELECT *
FROM T_REG2
WHERE REGEXP_LIKE(IP, '^[10]{2}\.[01]{2}\.[10]{2}');
-- 172.16.168.xxx 의 ip 를 출력하고 싶을 경우
SELECT *
FROM T_REG2
WHERE REGEXP_LIKE(IP, '^[172]{3}\.[16]{2}\.[168]{3}'); -- 168을 직접 적는 경우는
-- 특정 조건을 제외한 결과 출력하기
-- ~가 아니다라는 뜻으로 NOT을 사용
-- 알파벳을 포함하지 않은 행만 출력
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[A-Za-z]');
-- 숫자를 포함하지 않은 행만 출력
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[0-9]');
-- 사용 예제 5 : 특수 문자 찾기
-- ! 문자가 들어가는 행을 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '!');
-- 특수문자 ?, *, . - ? , * , . 등은 메타캐릭터
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '?'); -- 모든것의 의미
-- 특수문자 \?, \*, \.
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '\?');
-- 특수문자가 없는 경우
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '\?');
-- 2) REGEXP_REPLACE 함수
-- REPLACE 함수를 확장한 개념으로 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로
-- 치환하는 함수
-- 문 법: REGEXP_REPLACE (source_char, pattern // 원본 데이터, 찾고자 하는 패턴
-- [, replace_string // 변환하고자 하는 형태
-- [, position // 검색 시작위치(기본값 1)
-- [, occurrence // 패턴과 일치가 발생하는 횟수 0(모든값 대체), N(N번째 발생 대체)
-- [, match_param]]]] // 기본값으로 검색되는 옵션을 변경 (c:대소문자 구분(O), i:대소문자 구분(X), m:검색조건 여러줄)
-- )
-- 사용 예제 1 : 모든 숫자를 특수 기호로 변경하기
SELECT TEXT, REGEXP_REPLACE(TEXT, '[[:digit:]]', '*')
FROM T_REG;
--사용 예제 2: 특정 패턴을 찾아서 패턴을 변경하기
-- 숫자를 찾아서 숫자 뒤에 ‘-*’ 를 추가하는 예제입니다.
-- ( ) 주의!! : ( ) 사이에 있는 것을 찾아서...
SELECT TEXT, REGEXP_REPLACE(TEXT, '([0-9])', '\1-*')
FROM T_REG;
-- t_reg2 테이블에서 ip의 .(dot) 부분을 모두 삭제하고 출력
SELECT NO, IP, REGEXP_REPLACE(IP, '\.', '')
FROM T_REG2;
-- 첫 번째 .(dot) 만 '/' (슬래쉬) 기호로 변경하고 싶을 경우
SELECT NO, IP, REGEXP_REPLACE(IP, '\.', '/', 1, 1)
FROM T_REG2;
-- - 사용 예제 3: 이번 예는 특별히 아주 중요한 방법이니 꼭 기억하세요.
-- 사용자에게 입력 받은 문자가운데 공백이 여러 개 들어 있을 경우 그 공백을 제거시키는 방법
SELECT REGEXP_REPLACE('AAA BBB CCC', '( ){2,}', '')
FROM DUAL;
-- 공백을 * 로 치환하기
SELECT TEXT, REGEXP_REPLACE(TEXT, '( ){1,}', '*') -- 변경해보기
FROM T_REG;
-- [0-9]{2} :숫자 두 자리를 의미
-- abc[7-9]{2} :abc77,abc87, abc97 등이 해당
-- 사용 예제 4:
-- 사용자가 검색어를 입력할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도 공백이 있어서 모든 공백을 제거하기
SELECT STUDNO, NAME, ID
FROM STUDENT
-- WHERE ID = '75true' ; --REGEXP_REPLACE('&ID', '( ){1,}', ' ');
WHERE ID = REGEXP_REPLACE('&ID', '( ){1,}', '');
-- -사용 예제 5: 특정 문자열의 형태를 다른 형태로 바꿀 때
-- 아래 화면은 ‘20141023’ 형태로 이루어진 데이터를 ‘2014-10-23’ 의 형태로 변형하는 예제입니다.
SELECT '20181020', TO_DATE('20181020'),
REGEXP_REPLACE('20181020', '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})', '\1-\2-\3')
FROM DUAL;
-- 3) REGEXP_SUBSTR 함수
-- SUBSTR함수의 확장판으로 특정 패턴에서 주어진 문자를 추출해 내는 함수
-- 첫 글자가 공백이 아니고( ’[^ ]’) 그 후에 ‘DEF’ 가 나오는 부분을 추출
SELECT REGEXP_SUBSTR('ABC* *DEF $GHI%KJL', '[^ ]*[DEF]')
FROM DUAL;
-- 예1 : -- 교수테이블(professor )테이블에서 홈페이지(hpage) 주소가 있는 교수들만
-- 조사해서 아래의 화면처럼 나오게 출력하세요.
-- HTTP:// 부분을 제거하고 .으로 구분되는 필드를 최소 3개에서 4개까지 출력하라는 의미
-- + : 1회이상, ?: 0 또는 1회
SELECT NAME, HPAGE, SUBSTR(HPAGE, 8),
SUBSTR(HPAGE, INSTR(HPAGE, '/', 1, 2)+ 1),
LTRIM(REGEXP_SUBSTR(HPAGE, '/([[:alnum:]]+\.?){3,4}?'), '/') -- 숫자 변형해 보기
FROM PROFESSOR
WHERE HPAGE IS NOT NULL;
-- 예2 : Professor 테이블에서 101번 학과와 201번 학과 교수들의 이름과 메일 주소의
-- 도메인 주소를 출력하세요. 단 메일 주소는 @뒤에 있는 주소만 출력하세요.
SELECT NAME, EMAIL, SUBSTR(EMAIL, INSTR(EMAIL, '@')+1),
REGEXP_SUBSTR(EMAIL, '@([[:alnum:]]+\.?){3,4}?'),
LTRIM(REGEXP_SUBSTR(EMAIL, '@([[:alnum:]]+\.?){3,4}?'), '@')
FROM PROFESSOR
WHERE DEPTNO IN (101, 201);
-- 특정 기호나 문자를 기준으로 데이터 추출
-- 문자열에서 : 기호를 기준으로 2/3번 째의 문자열을 출력
SELECT REGEXP_SUBSTR('SYS/ORACLE@RACDB:1521:RACDB', '[^:]+', 1, 2)
FROM DUAL;
-- 여러 기호를 기준으로
SELECT REGEXP_SUBSTR('SYS/ORACLE@RACDB:1521:RACDB', '[^/:]+', 1, 2)
FROM DUAL;
--------------------------------------------
-- 11g 에서 추가된 정규식 함수
---------------------------------------------------
-- 1) REGEXP_COUNT 함수 : 특정 문자의 개수를 세는 함수
-- 대소문자 A/a 의 개수
SELECT TEXT, REGEXP_COUNT(TEXT, '[Aa]')
FROM T_REG;
-- 검색 위치를 3으로 지정해서 3번째 문자 이후부터 해당 소문자 ‘c’ 가 나오는 개수를 세는 예제
SELECT TEXT, REGEXP_COUNT(TEXT, 'c', 3)
FROM T_REG;
-- 소문자 i 옵션을 줘서 대소문자 구분 없이 (즉 'C' 와 'c' 모두) 몇 개가 나오는 지 세어 출력하기
SELECT TEXT, REGEXP_COUNT(TEXT, 'c', 1, 'i')
FROM T_REG;
-- . 과 \. 의 차이
SELECT TEXT, REGEXP_COUNT(TEXT, '.'), REGEXP_COUNT(TEXT, '\.')
FROM T_REG;
-- 어떤 문자를 검색할 때 사용하는 3가지 방법을 보여 주는데 어떤 방법으로 검색하든 동일한 결과가 나옵니다.
SELECT TEXT,
REGEXP_COUNT(TEXT, 'aa'),
REGEXP_COUNT(TEXT, 'a{2}'),
REGEXP_COUNT(TEXT, '(a)(a)')
FROM T_REG;