[Oracle] 오라클 REGEXP_COUNT 함수 사용법

오라클 SQL에서 정규식을 사용하여 문자열에 포함된 구분자의 개수 또는 문자나 문자열의 개수를 구할 때는 REGEXP_COUNT 함수를 사용하면 된다. REGEXP_COUNT 함수는 오라클 11g 이상에서 사용할 수 있으므로 하위 버전에서는 다른 방법을 사용해야 한다.

 

REGEXP_COUNT("문자열", "정규표현식", "시작위치", "일치옵션")
시작위치부터는 사용하지 않을 경우 생략할 수 있다.

 

목차 기본 사용법 숫자, 특수문자, 영문자, 한글 개수 세기 고급 활용 예제

 

기본 사용법

SELECT REGEXP_COUNT('AAA,BBB,CCC,DDD', ',') AS result1
     , REGEXP_COUNT('AAA,BBB,CCC,DDD', 'A') AS result2
  FROM dual

 

 

REGEXP_COUNT 함수를 사용하여 문자열에 포함된 구분자(특수문자)를 세거나,

특정 문자 또는 문자열을 개수를 셀 수 있다.

 

SELECT REGEXP_COUNT('AAA,BBB,CCC,DDD', ',')    AS result1
     , REGEXP_COUNT('AAA,BBB,CCC,DDD', ',', 5) AS result2
  FROM dual

 

 

세 번째 인자에 시작 위치를 지정하면, 지정한 위치부터 문자열을 검색 후 개수를 센다.

 

SELECT REGEXP_COUNT('AAA,BBB,CCC,DDD', 'a')         AS result1
     , REGEXP_COUNT('AAA,BBB,CCC,DDD', 'a', 1, 'i') AS result2
  FROM dual

 

 

네 번째 인자에 "i"를 입력하면 대소문자 구분 없이 문자열을 검색한다.

 

숫자, 특수문자, 영문자, 한글 개수 세기

SELECT REGEXP_COUNT('abc@ABC@123@가나다', '\d')          AS result1
     , REGEXP_COUNT('abc@ABC@123@가나다', '[0-9]')       AS result2
     , REGEXP_COUNT('abc@ABC@123@가나다', '[[:digit:]]') AS result3
  FROM dual

 

 

\d, [0-9], [[:digit:]]는 모두 숫자를 의미하므로, 자신에게 편한 형식을 사용하면 된다.

문자 클래스( [ ] )를 사용할 경우 윈 하는 숫자 범위를 지정할 수 있다. (예, 0~5 → [0-5])

 

SELECT REGEXP_COUNT('abc@ABC@123@가나다', '[[:punct:]]') AS result1
     , REGEXP_COUNT('abc,ABC,123,가나다', '[[:punct:]]') AS result2
     , REGEXP_COUNT('abc/ABC/123/가나다', '[[:punct:]]') AS result3
  FROM dual

 

 

[[:punct:]]는 모든 특수문자를 의미한다. 문자열에 포함된 모든 특수문자 개수를 센다.

특정 특수문자만 검색하고 싶다면, 문자열 클래스( [ ] )를 사용하면 된다. (예, [!@#$%&,])

 

SELECT REGEXP_COUNT('abc@ABC@123@가나다', '[a-zA-Z]') AS result1
     , REGEXP_COUNT('abc@ABC@123@가나다', '[a-z]')    AS result2
     , REGEXP_COUNT('abc@ABC@123@가나다', '[A-Z]')    AS result3
  FROM dual

 

 

[a-zA-Z]는 모든 영문자, [a-z]는 소문자, [A-Z]는 대문자를 찾을 때 사용할 수 있다.

[[:alpha:]]는 모든 영문자를 의미하지만 한글까지 체크할 수 있으므로 사용 시 주의해야 한다.

 

SELECT REGEXP_COUNT('abc@ABC@123@가나다', '[가-힣]') AS result
  FROM dual

 

 

[가힣]은 한글을 찾을 때 사용할 수 있다.

 

고급 활용 예제

 SELECT REGEXP_SUBSTR('abc,ABC,123,가나다', '[^,]+', 1, LEVEL) AS result
   FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('abc,ABC,123,가나다', ',') + 1

 

 

REGEXP_COUNT 함수로 구분자의 개수를 구하여, 구분자 개수만큼 REGEXP_SUBSTR 함수로 문자열을 잘라서 행으로 변환할 수 있다. 구분자 개수 + 1을 해줘야 마지막 값까지 자를 수 있다.

 

WITH temp AS
(
    SELECT 'MANAGER' job, 'BLAKE,JONES,CLARK' ename_list FROM dual
    UNION ALL
    SELECT 'SALESMAN' job, 'ALLEN,TURNER,MARTIN,WARD' ename FROM dual
)

SELECT a.job
     , REGEXP_SUBSTR(a.ename_list, '[^,]+', 1, b.row_cnt) AS ename
  FROM temp a
     , (SELECT LEVEL row_cnt FROM dual CONNECT BY LEVEL <= 10) b --구분자 최대 개수
 WHERE b.row_cnt <= REGEXP_COUNT(a.ename_list, ',') + 1
 ORDER BY job, ename

 

 

첫 번째 문자열을 구분자로 잘라서 행으로 변환하는 예제를 조금 더 응용하여, 실제 테이블의 데이터에서 특정 칼럼의 값을 구분자로 잘라서 행으로 변환하는 예제이다. b 테이블에서 최대 구분자가 올 수 있는 개수를 지정해 줘야 한다.

[Oracle] 정규식 사용법 쉽게 설명 (REGEXP)

[Oracle] 오라클 REGEXP_SUBSTR 함수 사용법

[Oracle] 오라클 REGEXP_LIKE 함수 사용법

[Oracle] 오라클 REGEXP_REPLACE 함수 사용법

[Oracle] 오라클 REGEXP_INSTR 함수 사용법

[Oracle] 문자열에서 특정 문자 개수 (10g 이하)