[Oracle] 단일행 함수 - 정규식(Regular Expression) (1)

정규식(Regular Expression)

• SQL 문장에서도 유닉스에서 사용하는 정규식을 사용해 다양한 검색 가능
• 정규식(Regular Expression)
  - 유닉스에서 검색 할 때 주로 사용되는 기술
  - 다양한 메타문자들을 사용해 검색 방법을 확장하는 것
• 이는 오라클 10g 버전부터 추가된 기능

 

오라클 like 구문 업그레이드 regexp_like 샘플예제 (정규표현식 기본 응용)

 

• 솔라리스, 리눅스 정규식 예시
  • ^(캐럿)
    - 해당 문자로 시작하는 line 출력
    - [ex] '^pattern'
  • $(달러)
    - 해당 문자로 끝나는 line 출력
    - [ex] 'pattern$'
  • .
    - S로 시작해 E로 끝나는 line ( . → 1 character )
    - [ex] 'S . . . .E'
  • *
    - 모든이라는 뜻
    - 글자 수가 0일 수 있음
    - [ex] '[a-z]*'
  • [ ]
    - 해당 문자에 해당하는 한 문자
    - [ex] '[Pp]attern'
  • [^]
    - 해당 문자에 해당하지 않는 한 문자
    - [ex] '[^a-m]attern'
    - ^ 기호를 안에 넣으면 제외하는 것이며, 괄호 밖에 위치하는 경우 해당하는 문자로 시작하는 데이터 출력
  • { } 반복 기호
    - a{5}  ==> aaaaa
    - a{3,} ==> aaa, aaaa, aaaa, ...
    - a{3,5} ==> aaa, aaaa, aaaaa
    - ab{2,3} ==> abb, abbb
    - [0-9]{2} ==> 숫자 두 자리
    - abc[7-9] ==> abc77, abc87, abc97 등 해당
  • [ ] 대괄호 안에 나열할 경우 or을 쓴 것과 같음
  • \1 앞에 패턴 두 번 반복
• 위 기호들을 SQL에 사용하여 검색을 더욱 편리하고 강력하게 만들 수 있음
•  문자클래스 ==> [[:문자클래스명:]]
  • alpha --> [[:alpha:]]
  • blank --> [[:blank:]]
  • cntrl --> [[:cntrl:]]
  • digit --> [[:digit:]]
  • graph --> [[:graph:]]
  • lower --> [[:lower:]]
  • upper --> [[:upper:]]
  • print --> [[:print:]]
  • space --> [[:space:]]
  • xdigit --> [[:xdigit:]]
  • alnum --> [[:alnum:]] 문자와 숫자 동시 사용

---

REGEXP_LIKE 함수

 

REGEXP_LIKE ( 컬럼명, '정규식' )

 

• like 함수처럼 특정 패턴과 매칭되는 결과를 검색하는 함수
• WHERE 절에서만 사용 가능

예제

• 예시
  
  • 소문자를 포함하는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[a-z]');
    
    
  • 소문자 대문자 모두 포함하는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[a-zA-Z]' );
    
    
  • 소문자로 시작하고 공백을 포함하는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[a-z] ' );
    
    
  • 공백으로 시작하고 소문자를 포함하고 있는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, ' [a-z]' );
    
    
  • 소문자로 시작하고 공백 없이 뒤에 숫자를 포함하고 있는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[a-z][0-9]' );
    
    
  • 공백이 있는 모든 데이터를 찾고 싶은 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[[:space:]]' );
• 연속적인 글자 수 지정
  • 대문자가 연속적으로 두 글자 이상 오는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[A-Z]{2}');
    
    
  • 대문자와 숫자가 연속으로 각각 두 글자 이상 오는 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[A-Z][0-9]{2}' );
    
    
  • 모든 대문자를 출력하고 싶은 경우
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[A-Z]');
    SELECT * FROM t_reg WHERE REGEXP_LIKE( text, '[[:upper:]]');
• 특정 위치 지정해 출력
  
  - 문자 지정시 ^(캐럿) 문자 사용
  - 끝나는 문자 지정시 $(달러) 사용
  
  
  • 첫 시작을 대문자나 소문자로 하는 행 출력
    SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[A-Za-z]');
  • 첫 시작을 숫자나 대문자로 시작하는 모든 행을 출력
    SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[0-9A-Z]');
  • 소문자로 끝나는 행 출력
    SELECT * FROM emp WHERE REGEXP_LIKE( ename, '[a-z]$');
    SELECT * FROM emp WHERE REGEXP_LIKE( ename. '[[:alpha:]]$');
• 여러 조건 연결해 사용할 경우 : 바 기호 |
  • 소문자로 시작하거나 숫자로 시작하는 화면 출력 
    SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[a-z] | ^[0-9]');
  • 첫 글자가 M으로 시작하고 두 번째 글자가 a 혹은 o가 오는 이름
    SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^M(a|o)');
• 소문자로 시작하지 않는 데이터
  SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[^a-z]');
  
  
• 숫자로 시작하지 않는 데이터
  SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[^0-9]');
  
  
• 소문자나 숫자로 시작하지 않는 데이터
  SELECT * FROM emp WHERE REGEXP_LIKE( ename, '^[^0-9a-z]');
  
  
• 소문자가 들어 있는 모든 행 제거
  SELECT * FROM T_REG WHERE NOT REGEXP_LIKE( text, '[a-z]' );
  
  
• 정규식에서의 문자 인식 : \
  SELECT * FROM student WHERE REGEXP_LIKE( tel, '[0-9]{2}\)[0-9]{4}');
  
  
• 특정 조건을 제외한 모든 행 출력 : NOT
  
  • 영문자(대소문자)를 미포함한 행 출력
    SELECT * FROM t_reg WHERE NOT REGEXP_LIKE( text, '[a-zA-Z]');
• 특수문자 찾기
  • 느낌표 ( ! )가 들어간 데이터 찾기
    SELECT * FROM T_REG WHERE REGEXP_LIKE( text, '!' );
  • 물음표( ? ) 혹은 * 은 SQL에서 모든 것을 가진 메타 캐릭터 문자로 REGEXP_LIKE에 조건으로 바로 적을 경우 모든 행이 출력됨
  • 이를 문자로 인식하게 하기 위해서는 탈출 문자 ( \ ) 사용

---

REGEXP_REPLACE 함수

- REPLACE 함수의 확장 개념

- 특정 패턴을 찾아 주어진 다른 모양으로 치환

- SELECT 절에 사용됨

 

- 문법

REGEXP_REPLACE( source_char, pattern
                                         [, replace_string [, position
                                         [,  occurrence, [, match_param ]]]] )

• source : 원본 데이터
  • 컬럼, 문자열
  • 올 수 있는 데이터 타입: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB
• pattern : 찾고자 하는 패턴
  • 512바이트 까지의 정규 표현식
  • 올 수 있는 데이터 타입: CHAR, VARCHAR2, NCHAR, NVARCHAR2
• replace_string : 변화하고자 하는 형태
  • 두 번째 패턴에 일치하는 문자(또는 문자열)을 세 번째 모양(replace_string)으로 변경
• position : 검색 시작 위치 지정
  • 기본 값: 1
• occurrence : 패턴과 일치가 발생하는 횟수
  • 0은 모든 값을 대체함
  • 다른 숫자 n을 주면 n번쨰 발생하는 문자열 대입
• match_parameter : 기본 값으로 검색되는 옵션 바꾸기 가능
  • c : 대문자 구분해 검색
  • i : 대소문자 구분하지 않고 검색
  • m : 검색 조건을 여러 줄로 줄 수 있음
    
    
  • c와 i가 중복으로 설정되면 마지막에 설정된 값을 사용함
    (ex) ic ---->  c 옵션 적용

---

• 예제 1) 모든 숫자를 특수 기호로 변경
  SELECT TEXT, REGEXP_REPLACE(TEXT, '[[:digit:]]', '*') FROM T_REG;
  
  
• 예제 2) 특정 패턴 찾아 패턴 변경 
  (ex) 숫자 뒤에 '-*' 추가
  
  SELECT TEXT, REGEXP_REPLACE(TEXT,  '([0-9])', '\1-*') FROM T_REG;
  
  - repalce pattern에 숫자 1을 넣을 경우 모든 숫자를 인식하고 진행하지만
  - 1 외에 숫자를 넣을 경우 숫자를 인식하지 못함
  
  
• 예제 3) 입력 받은 문자 가운데 공백이 여러 개 있을 경우 공백 제거
  SELECT REGEXP_REPLACE('aaa bbb', '( ){1,}', '') FROM dual;
  
  
• 예제 4-1) 검색어 입력 시 공백 문자를 가장 먼저 입력하고, 아이디 중간에도 공백이 있는 경우
  SELECT studno, name, id FROM student
  WHERE id = REGEXP_REPLACE('&data', '( ){1,}', '');
  
  
• 예제 4-2) 예제 4번에서 대소문자 구분 없이 모두 소문자로 바꾸어 조회하고 싶은 경우
  SELECT studno, name, id FROM student
  WHERE id = LOWER( REGEXP_REPLACE('&data', '( ){1,}', '' ) );
  
  
• 예제 5) 특정 문자열 형태를 다른 형태로 변형할 경우
  SELECT REGEXP_REPLACE('20210727', '([[:digit:]]){4}([[:digit:]]){2}([[:digit:]]){2}', '\1 : \2 : \3') FROM dual;
  
  - 소괄호를 기준으로 맨 왼쪽에서부터 1번부터 번호가 부여됨
  - 역슬래쉬(\)와 해당 출력 번호 입력시 입력한 포멧 순서대로 출력됨

---

REGEXP_SUBSTR 함수

- SUBSTR 함수의 확장판

- 특정 패턴에서 주어진 문자 추출

- ★ 특정 기호를 기준으로 원하는 데이터 출력 가능 

- 사용 예시. http를 제외한 url 추출

 

- 구문

REGEXP_SUBSTR( source_char, pattern, [, position [, occurrence [, match_param [, subexpr ]]]] )

• source_char : 대상 문자열
• pattern : 정규 표현식 패턴
• position : 문자열 내에서 처음 시작하는 위치
• occurrence : 몇 번째 일치하는가
• match_param : 일치 행동 변경
  - i : 대소문자 구별 안 함
  - c : 대소문자 구별
  
  
• 예제 1) 교수 테이블에서 홈페이지 주소가 있는 교수만 조사해 출력할 것 http:// 제외
  SELECT name, LTRIM( REGEXP_SUBSTR( hpage,  '/([[:alnum:]]+\.?){3,4}?', '/' ) FROM Professor;
  
  
• 예제 2) :을 기준으로 두 번쨰 문자 출력
  SELECT REGEXP_SUBSTR('teset:dat/a:muyaho', '[^:]+', 1, 2) FROM dual;