[Oracle] 오라클 REGEXP_SUBSTR 함수 사용법
- 데이터베이스/오라클
- 2024. 3. 9.
오라클 SQL에서 정규식을 사용하여 문자열을 자르기 위해서는 REGEXP_SUBSTR 함수를 사용하면 된다. REGEXP_SUBSTR 함수는 SUBSTR 함수에서 정규식을 사용할 수 있도록 확장한 함수라고 생각하면 된다. 정규식 함수를 사용하기 위해서는 정규식 표현식을 조금 이해하고 있어야 어렵지 않게 느낄 수 있을 것이다.
REGEXP_SUBSTR("문자열", "정규표현식", "시작위치", "찾은횟수","대소문자구분", "그룹순번")
시작위치 항목부터는 필요하지 않을 경우 생략할 수 있다.
| 목차 |
구분자로 문자열 자르기
SELECT REGEXP_SUBSTR('AAA,BBB,CCC,DDD', '[^,]+') AS result1
, REGEXP_SUBSTR('AAA,BBB,CCC,DDD', '[^,]+', 1, 2) AS result2
, REGEXP_SUBSTR('AAA,BBB,CCC,DDD', '[^,]+', 1, 3) AS result3
, REGEXP_SUBSTR('AAA,BBB,CCC,DDD', '[^,]+', 1, 4) AS result4
FROM dual
 |
쉼표를 기준으로 문자열을 자르는 예제이다.
[^,] → 쉼표(,)가 아닌 문자
+ → 1개 이상의 문자
SELECT REGEXP_SUBSTR('2024-03-08', '[^-/.]+') AS date_year
, REGEXP_SUBSTR('2024/03/08', '[^-/.]+', 1, 2) AS date_month
, REGEXP_SUBSTR('2024.03.08', '[^-/.]+', 1, 3) AS date_day
FROM dual
 |
여러개의 구분자를 사용하여 문자열을 자를 수 있다.
문자열에서 숫자 추출 하기
SELECT REGEXP_SUBSTR('Oracle9i Database', '\d+') AS result1
, REGEXP_SUBSTR('Oracle Database 11g', '[0-9]+') AS result2
, REGEXP_SUBSTR('Oracle Database 23c', '[[:digit:]]+') AS result3
FROM dual
 |
문자열에서 숫자를 추출하는 예제이다.
\d, [0-9], [:digit:]는 모두 숫자를 의미이며, 익숙한 형식을 사용하면 된다.
SELECT REGEXP_SUBSTR('Oracle9i Database Release 2', '\d+[igc]') AS result1
, REGEXP_SUBSTR('Oracle Database 11g Release 1', '\d+[igc]') AS result2
, REGEXP_SUBSTR('Oracle Database 23c', '\d+[igc]') AS result3
FROM dual
 |
오라클 버전과 버전 이니셜 문자를 함께 추출하는 예제이다.
위의 예제에서 문자열 클래스( [igc] )의 의미를 이해하는 것이 중요하다.
문자열에서 단어 추출 하기
SELECT REGEXP_SUBSTR('Oracle Database 11g Release 1', 'Ora[a-zA-Z]+') AS result1
, REGEXP_SUBSTR('Oracle Database 11g Release 1', 'Dat[[:alpha:]]+') AS result2
FROM dual
 |
[a-zA-Z], [:alpha:]은 모두 알파벳을 의미하며, 익숙한 형식을 사용하면 된다.
SELECT REGEXP_SUBSTR('Oracle Database 11g Release 1', '[a-zA-Z]+ase') AS result1
, REGEXP_SUBSTR('Oracle Database 11g Release 1', '[a-zA-Z]+ase', 1, 2) AS result2
FROM dual
 |
"Database"와 "Release" 문자열을 추출할 수 있다.
문자열에서 날짜 추출 하기
SELECT REGEXP_SUBSTR('Oracle 21c 2020-12-08 Release', '\d{4}-\d{2}-\d{2}') AS rel_date
FROM dual
 |
문자열에서 숫자(4)-숫자(2)-숫자(2) 형식의 날짜 문자열을 추출한다.
SELECT REGEXP_SUBSTR('Oracle 21c 2020-12-08 Release', '(\d{4})-(\d{2})-(\d{2})') AS rel_date
, REGEXP_SUBSTR('Oracle 21c 2020-12-08 Release', '(\d{4})-(\d{2})-(\d{2})', 1, 1, 'i', 1) AS rel_year
, REGEXP_SUBSTR('Oracle 21c 2020-12-08 Release', '(\d{4})-(\d{2})-(\d{2})', 1, 1, 'i', 2) AS rel_month
, REGEXP_SUBSTR('Oracle 21c 2020-12-08 Release', '(\d{4})-(\d{2})-(\d{2})', 1, 1, 'i', 3) As rel_day
FROM dual
 |
그룹(괄호)으로 각 항목을 묶을 경우, 추출된 날짜에서 다시 한번 년, 월, 일을 분리하여 추출할 수 있다.
REGEXP_SUBSTR 함수에서 그룹순번 항목은 오라클 11g 이상부터 사용할 수 있다.
URL에서 HOST 추출 하기
SELECT REGEXP_SUBSTR('https://gent.tistory.com/625', 'https://([^/]+)', 1, 1, 'i', 1) AS result1
, REGEXP_SUBSTR('https://docs.oracle.com', 'https://([^/]+)', 1, 1, 'i', 1) AS result2
FROM dual
 |
"https://" 문자열로 시작해서 1개 이상의 문자열을 찾고, "/"을 만날 때까지 문자열을 추출한다.
"/"가 없을 경우 문자열 끝까지 추출한다.
HTML 태그 값 추출 하기
WITH temp AS (
SELECT '<html>'
|| ' <head>'
|| ' <title>젠트의 프로그래밍 세상</title>'
|| ' </head>'
|| ' <body>'
|| ' <h1>오라클 REGEXP_SUBSTR 함수 사용법</h1>'
|| ' </body>'
|| '</html>' AS html_string
FROM dual
)
SELECT REGEXP_SUBSTR(html_string, '(<h1>)(.*?)(</h1>)', 1, 1, 'i', 2) AS tag_val
FROM temp
 |
정규식 함수를 사용하여 HTML 태그의 값을 추출하는 예제이다. 위의 예제는 단순하게 하나의 태그 값만 추출하였지만 조금 더 응용하면 특정 태그 값을 연속적으로 추출할 수 있다.
정규 표현식이 익순하지 않으면 조금 어려울 수도 있다. 아래의 링크를 참고하여 다양한 예제를 접한다면 이해하는데 도움이 될 수 있을 것이다.
[Oracle] 정규식 사용법 쉽게 설명 (REGEXP)
오라클 10g부터 정규식을 사용할 수 있도록 함수가 추가되었다. 정규식을 사용하면 문자열을 패턴으로 찾거나 자를 수 있기 때문에 기존의 복잡하게 구현된 쿼리문을 정규식 함수를 사용하여 간
gent.tistory.com
[Oracle] 오라클 REGEXP_LIKE 함수 사용법
오라클 SQL에서 정규식 LIKE를 사용하기 위해서는 REGEXP_LIKE 함수를 사용하면 된다. 정규식을 사용하면 숫자, 특수문자 등을 쉽게 검색할 수 있고 날짜, 전화번호 등 형식이 있는 문자열도 패턴을
gent.tistory.com
[Oracle] 문자열에서 HTML 태그 값 추출 방법
오라클에서 HTML 문자열 내부의 특정 태그 값을 추출하기 위해서는 정규식 함수를 사용하면 쉽게 해결된다. 정규식 함수는 Oracle 10g 이상에서 사용할 수 있으며, Oracle 11g 이상에서는 XMLTYPE 함수를
gent.tistory.com
[Oracle] 문자열에서 괄호 안 문자, 숫자 추출 방법
오라클에서 문자열에서 괄호 안의 텍스트, 글자, 문자, 숫자를 추출할 때는 정규식 함수를 사용하면 쉽게 추출할 수 있다. 그러나 오라클 9i 이하인 경우 정규식 함수를 사용할 수 없기 때문에 SUB
gent.tistory.com
[Oracle] 문자열에서 한글만 추출 방법 (정규식)
오라클에서 칼럼의 문자열에서 한글이 아닌 문자를 제거하고 한글만 추출해야 하는 경우가 있다. 문자열에서 한글만 추출할 때는 정규식 함수를 사용하면 쉽게 해결된다. 오라클에서 정규식 함
gent.tistory.com