정규식 : 데이터의 간단한 및 복잡한 패턴을 검색하고 조작할 수 있는 식
*메타문자(검색 알고리즘을 지정하는 연산자)와 리터럴(검색중인 문자)로 구성됨.
Function | Explanation |
REGEXP_LIKE | 일반적인 LIKE보다 복잡한 패턴의 데이터 검색 가능한 함수 |
REGEXP_REPLACE | 일반적인 REPLACE보다 복잡한 데이터 교체 가능한 함수 |
REGEXP_INSTR | 일반적인 INSTR보다 복잡한 데이터 찾기 가능한 함수 |
REGEXP_SUBSTR | 정규식 패턴을 검색하고 일치부분 출력해주는 함수 |
REGEXP_COUNT | 특정 단어가 몇번 출력되는지 출력해주는 함수 |
ex. 정규식을 통한 한글, 영문, 특수문자 및 숫자 제거 방법
1. 한글 제거
ex. SELECT REGEXP_REPLACE('ㄱㄴㄷㄹㅁ(12)abc','[가-힣]','') AS RESULT FROM DUAL;
2. 숫자 제거
ex. SELECT REGEXP_REPLACE('ㄱㄴㄷㄹㅁ(12)abc','[0-9]','') AS RESULT FROM DUAL;
3. 영문 제거
ex. SELECT REGEXP_REPLACE('ㄱㄴㄷㄹㅁ(12)abc','[a-z]','') AS RESULT FROM DUAL;
4. 한글과 특수문자() 제거
ex. SELECT REGEXP_REPLACE('ㄱㄴㄷㄹㅁ(12)abc','[가-힣()]','') AS RESULT FROM DUAL;
REGEXP_REPLACE 함수
- 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수
사용 예제 1 : 모든 숫자를 특수 기호로 변경하기
‘[[:digit:]] 부분은 [:문자클래스:]의 형태로 표현을 합니다. 그리고 “문자 클래스”에 들어갈 수 있는내용은 alpha, blank, cntrl, digit, graph, lower, print, space, upper, xdigit의 종류가 있습니다.
많이 사용되는 것의 예를 들면 [:digit:]는 [0-9]의 의미이고 [:alpha:]는 [A-Za-z]와 같은 의미를 나타내고 [:space:]는 공백을 의미합니다.
사용 예제 2 : 특정 패턴을 찾아서 패턴을 변경하기
숫자를 찾아서 숫자 뒤에 ‘-*’를 추가하는 예제입니다.
비슷한 예로 아래 화면은 ip부분의 .(dot) 부분을 모두 삭제하고 출력하는 예입니다.
사용 예제 3 : 사용자에게 입력받은 문자 가운데 공백이 여러 개 들어 있을 경우 그 공백을 제거시키는 방법
예를 들어, 사용자 ID가 ‘aaa bbb’ 처럼 중간에 공백이 있을 경우 ‘aaa’와 ‘bbb’사이에 공백을 없애고 출력하는 방법입니다.
위 예제에서 {1,} 부분을 {1}로 해도 됩니다. 위 예에서 { } 내의 숫자는 앞 문자가 나타나는 횟수 또는 범위를 의미합니다. 예를 들어, a{5}의 의미는 ‘a’의 5번 반복인 aaaaa만을 의미합니다. 이 형태의 변형인 a{3,}은 ‘a’가 세 번 이상 반복인 aaa, aaaa, aaaaa, … 등을 의미합니다. 그리고 a{3, 5}의 의미는 aaa, aaaa, aaaaa를 의미하며 ab{2,3}은 뒤의 b가 두 번과 세 번 반복된 형태인 abb와 abbb를 의미합니다.
다음 예는 ‘abc bbb’에서 ( ) (괄호 사이는 공백)이 {2,} (두 칸 이상)인 것을 찾아서 “(공백을 제거)하라는 의미입니다. 그래서 그 결과로 공백이 한 칸인 첫 번째 ‘aaa bbb’값은 공백이 제거되지 않은 채 출력되었고 공백이 두 칸인 두 번째 ‘aaa bbb’은 공백이 제거되어 출력되었습니다.
다른 예를 한 가지 더 살펴보겠습니다.
이해가 되셨나요? 공백이 세 칸이여도. {2,}는 2칸 이상을 의미하다는 걸 잊지 않으셔야 합니다!
정규식을 잘 사용하면 SQL과 각 종 프로그램의 연산속도를 감소시키며 프로그램을 단순화 시킬 수 있습니다.
시간이 걸리더라도 한번씩 사용해보면 좋습니다.
. | 하나의 문자를 의미합니다. ".*"하면 한개이상의 문자 즉 모든 문자를 의미합니다. |
$ | 끝을 의미한다. AAA$ 하면 AAA로 끝나는 것을 의미한다. |
\ | 특수 문자를 문자로 인식할때 사용합니다. |
¦ | 여러 식 중에서 하나를 선택합니다. SQL의 OR조건과 같습니다. "abc¦adc"는 abc와 adc 문자열을 모두 포함한다. |
^ | 문자 클래스 안의 문자를 제외한 나머지를 선택한다. 예를 들면 [^abc]d는 ad, bd, cd는 포함하지 않습니다. [^a-z]는 알파벳 소문자로 시작하지 않는 모든 문자를 의미한다. |
[] | "["과 "]" 사이의 문자 중 하나를 선택한다. "¦"를 여러 개 쓴 것과 같은 의미이다. 예를 들면 [abc]d는 ad, bd, cd를 뜻한다. 또한, "-" 기호와 함께 쓰면 범위를 지정할 수 있다. "[a-z]"는 a부터 z까지 중 하나, "[1-9]"는 1부터 9까지 중의 하나를 의미한다. |
() | 여러 식을 하나로 묶을 수 있다. "abc¦adc"와 "a(b¦d)c"는 같은 의미를 가진다. |
* | 0개 이상의 문자를 포함한다. "a*b"는 "b", "ab", "aab", "aaab"를 포함한다. |
+ | "a+b"는 "ab", "aab", "aaab"를 포함하지만 "b"는 포함하지 않는다. [+ 앞 내용 기준] |
? | "a?b"는 "b", "ab"를 포함한다. [? 다음 내용 기준] |
{m} | "a{3}b"는 "aaab"만 포함한다. |
{m,} | "a{2,}b"는 "aab", "aaab", "aaaab"를 포함한다. "ab"는 포함되지 않는다. |
{m, n} | "a{1,3}b"는 "ab", "aab", "aaab"를 포함하지만, "b"나 "aaaab"는 포함하지 않는다 |
\n | 1에서 9까지의 숫자를 의미 |
\d | Matches a digit character. |
\D | Matches a nondigit character. |
\w | Matches a word character. |
\W | Matches a nonword character. |
\s | Matches a whitespace character. |
\S | matches a non-whitespace character. |
자주 사용하는 Class Syntex는 아래와 같습니다.
[:alnum:] | 영어와 숫자 |
[:alpha:] | 알파벳, [A-Za-z] |
[:blank:] | 공백과 탭 |
[:digit:] | 숫자 |
[:lower:] | 소문자, [a-z] |
[:punct:] | 특수문자 |
[:space:] | 공백(스페이스) |
[:upper:] | 대문자, [A-Z] |
[가-힣] | 한글전체 |
자주 사용하는 응용편
1. "["와 "]" 사이에 문자를 공백 처리하기, 괄호의 정의를 정하고 사이의 내용을 제거하면 됩니다.
regexp_replace(s, "\\[.*\\]", "")
2. 숫자와 문자를 제외하고 모두 제거
regexp_replace(nm, '[^A-Z0-9 ]', '')
3. 공백이 2개 이상인 부분을 제거
REGEXP_REPLACE('Kontext is a website for data engineers.','[\s]{2,}', '')
4. 끝에 문자가 _(으로 시작하고)_(으로 사작하지 않는) 문자로 끝나는 것
regexp_replace('The_quick brown fox jumped over the_fence', '_[^_]*$','')