[복습] SQL | Oracle 문자열의 규칙 찾기, 문자열의 일반화인 정규식 표현식(2)

지난 번 게시글에 이어서 정규식 표현식의 정리노트이다.

https://metime.tistory.com/91

[복습] SQL | Oracle 문자열의 규칙 찾기, 문자열의 일반화인 정규식 표현식 + 복습후기(20231117)

 

[정규식 표현식]
1. REGEXP_REPLACE

REGEXP_REPLACE(대상, 찾을문자열, 바꿀문자열, 검색위치, 발견횟수, 옵션)

총 이렇게 들어간다. 전 시간에는 바꿀문자열까지만 배웠었다.

 

(abc......abc......abc......123) 이라는 문자열이 있을 때, abc라는 것을 다 추출하고 싶은데 다수의 패턴을 추출하고자 할 때 sql은 여러 개를 동시 추출할 수가 없다. 한 자리에 담을 자료구조가 없기 때문이다.

그래서 하나만 추출할 수 있또록 추가적인 옵션이 들어갈 수밖에 없는 것이다. 그러나 몇 번째를 지우겠다는 것은 지정할 수 있다. 그래서 검색위치 옵션이 들어가는 것이다. (replace는 여러 개를 지우는 데에는 문제가 없다.)

 

특징으로는,

바꿀 문자열 삭제 시 다 삭제 된다(replace와 같다).

검색 위치는 생략 시 DEFAULT가 1이다.

발견횟수 생략시에는 DEFAULT가 0이다(0의 의미는 '모든')

 

옵션으로는

c: 대소 구분하여 검색(DEFAULT)

i: 대소를 구분하지 않고 검색

m: 이론적으로만 필요한 옵션. 패턴을 다중라인으로 선언할 수 있다. 원래 ENTER도 넣으면 ENTER도 적용된다. 그런데 이 옵션은 ENTER를 넣어도 ENTER 적용이 안된다.

 

예시로 확인해보자.

LINE 1의 경우, 한 글자의 문자로 표현했는데 다 지워졌다. 그 이유는 사실상 발견횟수가 0으로 전달되었기 때문이다(DEFAULT).

(0이 전체를 의미하는 옵션. 그냥 외우기. 100이 전체. 이렇게 할 수는 없으니까)

LINE 2는 성만 삭제되며, LINE 3은 두 번째 글자 마스킹 처리가 된다.

 

2. REGEXP_SUBSTR

REGEXP_SUBSTR(대상, 패턴, 검색위치, 발견횟수, 옵션, 추출그룹)

특징으로는 

검색위치 생략 시 1, 

발견위치 생략 시 1, 

추출그룹은 서브패턴을 전달하여 그 중 추출할 서브패턴 위치를 나타낸다.

선생님은 옵션을 NULL을 넣으셨다.

 

이런식으로 ()로 패턴의 위치를 지정하도록 넣어주며 괄호의 위치 순서에 따라 서브패턴 번호가 차례로 매겨진다.

이메일 ID 추출 방법이며, 네 가지 방법으로 다양하게 추출을 할 수가 있다.

모든 쿼리가 다 그렇긴 하지만 정규식 표현식은 정답이 없는 것 같았다.

 

 

 

3. REGEXP_INSTR

REGEXP_INSTR(대상, 패턴, 시작위치, 발견횟수, 옵션)

시작위치 생략시 1이며, 발견횟수 생략 시에도 1이다.

 

 

실습은 위와 같이 진행하였다.

INSTER과 비슷하였다.

 

REGEXP_INSTR의 오라클 공식문서 예시를 보면

 

위처럼 대괄호 바로 앞에 HAT(^) 기호가 들어가면 뒤에 있는 것을 부정한다는 뜻이 된다.

그래서 공백으로 구분된 단어의 위치 추출이 된다.

해석하자면 6번째에서 단어가 발견되어야 하며 그 위치는 37번째라는 의미이다.

 

^[ ]:  [] 안 패턴으로 시작하는
[^ ]: ^뒤에 오는 패턴을 제외한

 

 

4. REGEXP_LIKE

REGEXP_LIKE(대상, 패턴, 시작위치, 검색옵션)

은 조건절(WHERE, HAVING)에서만 사용할 수 있으며, 패턴 조건 검색 함수이다.

시작위치는 생략 시 1이 된다.

 

아래는 REGEXP_LIKE를 활용하여 ID 값이 숫자로 끝나는 직원을 출력하는 예시이다.

 

 

 

 

5. REGEXP_COUNT

REGEXP_COUNT(대상, 패턴, 시작위치, 옵션)

으로 패턴에 만족하는 값의 포함 횟수를 리턴한다.

ID에 포함된 숫자 개수를 리턴하는 쿼리이다.

 

 

아래 세 라인을 구분하도록 하자.

LINE 1은 [ ] 안 의 숫자와 하이픈을 다 세는 것이고,

LINE 2는 하이픈과 연결된 숫자가 몇 개인지 세는 것이고,

LINE 3은 숫자가 아닌 것을 세는 것이다.

 

 

다음은 한글을 지원하는 CHARACTER SET이다.

 

KO16MSWIN949

이게 기본이어서 힣 이라는 애가 지원이 가능하다.

이걸 왜 테스트 해보았냐면, 대괄호로 한글을 추출하는 방법이 있기 때문이다.

 

 

KSC5601에서는 힣이 지원이 안되고 힝이 지원되어서 한글이 세 개가 추출된다고 한다.

위에 캡쳐 두 개는 이해를 하지 못했지만 나중에 볼 일이 있을 것 같아 일단 올려두었다.