Database SQL_Single-Row Function_정규식

Step 2. Single-Row Function! 

- Sub : 정규식

 

동해로 서해로 강원도로 여름휴가를 다녀오느라..ㅎ 또 한동안 안썼었네요..ㅎ 여행으로 @_@;; 참 약속이 많은 8월..ㅎ

이제 다시 시작을 해보죠! 정규식! Oracle 10g부터 추가가 된 기능입니다. 정규식은 원래 유닉스에서 검색할때 쓰이는 기술입니다.

우선 정규식 기호부터 살펴보도록 하겠습니다.

 

사용 기호	의 미	사 용 예
^	Pattern으로 시작하는 line 출력	^pattern
$	Pattern으로 끝나는 line 출력	pattern$
.	P로 시작하여 n으로 끝나는 line(. -> 1 Character)	p....n
*	모든 이라는 뜻으로 글자수가 0 일수도 있음	[a-z]*
[]	Pattern에 해당하는 한 문자	[Pp]attern
[^]	Pattern에 해당하지 않는 한 문자	[^a-m]attern

 

우선 요렇게 된다는거!ㅎ 의미와 사용예를 잘 알아야 잘 쓸 수 있겠죠?ㅎ

 그럼 정규식함수에는 어떤것이 있을 까요?

함수	설명	비고
REGEXP_LIKE	like 함수처럼 특정 패턴과 매칭되는 결과 검색	10g
REGEXP_REPLACE	replace 함수의 확장한 개념. 특정 패턴을 찾아서 치환	10g
REGEXP_INSTR	특정 패턴이 출현한 첫 위치 값을 반환하는 함수	10g
REGEXP_SUBSTR	substr 함수의 확장한 개념. 특정 패턴에서 주어진 문자 추출	10g
REGEXP_COUNT	특정 문자의 개수를 세는 함수	11g
REGEXP_SUBSTR(SUB Expression)	10g의 regexp_substr의 확장 개념으로 서브 컬럼이 추가된 함수	11g

 

이것만해도 양이 엄청 많네요..ㅎ 문법도 좀 복잡복잡합니다. 얘네가..ㅎ 이제 차례차례 알아보는 것만 남았네요!!

순서대로 REGEXP_LIKE 부터 합니당. LIKE는 뭐라구요? 특정 패턴과 매칭되는 결과 검색입니다.

사용 예로는 엄청나게 많은 것을 할 수 있습니다. 다 다루는게 좋겠죠..? 그림이 좀 많아지겠어요..ㅎ

 

REGEXP_LIKE 예제 1 : 특정 문자나 숫자를 포함하는 결과 출력

 

 

 

요래요래 나타나는 것이 Like!입니다. 문법을 잠깐 보도록 할까요?

REGEXP_LIKE(컬럼이름, '기호') 가 되겠습니다. 어렵지 않아요 ㅎ 귀찮아서 그렇지..ㅎ

 

REGEXP_LIKE 예제 2 : 공백을 한 칸 포함하는 경우 찾기

 

 

 

두 대괄호 사이에 한칸 띄운거 보이시죠? 저렇게 공백을 표현 할 수 있습니다.

 

REGEXP_LIKE 예제 3 : 공백이 여러개일 경우 찾기

 

 

 

공백이 여러개여서 모를경우에는 어떻게 하냐구요? ?나 *을 넣어서 하시면 공백이 0개여도 1개여도 여러개여도 잘 나타납니다!

 

REGEXP_LIKE 예제 4 : 연속적인 글자 수 지정하기

 

 

 

 

연속적인 글자 수를 정하고 싶다면? 중괄호를 이용하시면 됩니다. 중괄호 3을 넣은 것이 보이죠? 앞에는 대문자구요.

대문자로 시작하는 글자가 연속적으로 세개가 오는 속성을 출력하라고 해서 아래와 같이 나왔습니다.

중괄호 앞 대문자 대신에 숫자, 소문자 등이 나와도 상관이 없다는건 아시겠죠?ㅎ 문법에 끼워 맞춰놓기만 하면 되니까요 ㅎ

 

REGEXP_LIKE 예제 5 : 시작되는 문자와 끝나는 문자 지정하기

 

 

 

앞에 표에서 시작되는 문자 검색은? '^' 캐럿이라고 했습니다. 그럼 끝표시는? '$' 달러였습니다. 잘 기억하시죠?ㅎ 그래도 쓰시면됩니다. 

 

 

 

그리고 또하나 |(or)의 개념입니다. 앞이거나 뒤이거나 둘중 하나일때 출력하기! 잘 기억하고 쓰시기 바랍니다.

 

 

 

이번에는 두개를 비교해 볼겁니다. NOT이라는 기호. ^(캐럿)의 쓰임과 NOT REGEXP_LIKE를 볼겁니다.

NOT이라는 뜻은? 아니다~ 겠죠 ㅎ 그럼 검색열에 들어가 있는 조건이 아닌 것은 출력하라고 하니까 저 조건을 빼고 출력하라는 거죠.

그런데 종류가 두가지가 있습니다. 저렇게 대괄호 앞에 쓰는 ^(캐럿)은 시작문자를 뜻하는 것이고 대괄호 안에 있는 아이는 NOT을 의미.

또한 WHERE절 바로 뒤에 REGEXP_LIKE이 오는데 그 앞에 NOT이 붙었죠? 이렇게 NOT은 두가지 형태로 쓰일 수 있습니다.

 

REGEXP_LIKE 예제 6 : 특수문자 찾기

 

 

 

특수문자를 찾는 것도 좀 애매~ 합니다. 그냥 '^[a-z]|[A-Z]|[0-9]' 해서하면 앞글자가 특수문자인것만 색출해 내는 것이고..하니까요 ㅎ

그래서 ?(물음표)가 들어간 것을 찾기 위해서는 저렇게 \(원표시)뒤에 물음표를 해주셔야 합니다.

여기서 \이것은 원표시가 아니라 escape 탈출이라는 뜻으로 쓰이는데요. 특수기호 ?의 뜻이 아닌 문자 그대로의 ?를 의미하라는 겁니다.

 

이렇게 LIKE에 대한 설명이 끗! 서진수쌤책 그대로 한거같아서 죄송하네요..ㅎ 그다음은 REGEXP_REPLACE 함수입니다.

앞에서 REPLACE 함수의 확장형이 REGEXP_REPLACE 함수라고 했습니다. 그만큼 옵션도 엄청 늘어났는데요..ㅎ

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

이렇게 입니다. 복잡복잡하죠? 문법조차..ㅎㅎ 차근차근 하나씩 보도록 할께요.

source_char는? 원본데이터입니다. 컬럼명이 될 수도있고 문자열이 될 수도 있습니다.

pattern은? 찾고자하는 패턴입니다.

replace_string은? 변환하고자 하는 형태입니다. pattern을 찾아서 replace_string으로 변환하라는 겁니다.

position은? 검색 시작 위치를 지정합니다. 생력하면 기본값은 1이구요 ㅎ substr같죠?ㅎ

occurrence는? 패턴과 일치가 발생하는 횟수를 의미합니다. 0은 모든값, n이라는 숫자는 n번째 발생하는 문자열을 대입합니다.

match_parameter는? 기본값으로 검색되는 옵션을 바꿀 수 있습니다.

-c : 대소문자를 구분, -i : 대소문자 구분안함, -m : 검색 조건을 여러 줄로 줌

이제 예제를 통해서 봅시다 ㅎ 문법이 어려우면 어려울수록 더욱 더 많이 테스트를 해봐야겠죠 ㅎ

 

 

 

우선 간단하게 치환하는 것을 보겠습니다. 숫자를 +로 치환! 해서 이렇게 나왔죠 ㅎ 이런식으로 해서 조건도 늘리면서 REGEXP_REPLACE를 쓰면 되겠습니다. 저도 헷갈리는 것이 많다보니 제대로 설명을 못드리겠네요.. 확실히 안다음에 나중에 추가 포스팅하겠습니다.

 

LIKE를 뒤로하고 REGEXP_INSTR 함수를 살펴보도록 하겠습니다.

INSTR의 경우 생각이 나시나요? 특정 문자의 위치를 출력해주는 함수였습니다. REPLACE_INSTR 또한 거의 같은 역할을 하는데요.

우선 예를 먼저 보도록 하겠습니다.

각 레코드에 특수문자 ?이 들어간 위치를 찾는 화면입니다. 이것만보면 그렇게 어렵지 않다는 것을 알 수 있습니다.

 

 

 

여기서 문법을 한번 살펴보고 가도록 하겠습니다.

REGEXP_INSTR(column, source_char, position, occurrence, return_opt, match_param, subexpr)

이렇게 이루어져 있습니다.

column의 경우는 아시다 싶이 기준이 되는 문자열이 있는곳, 또는 dual이나 스스로의 비교를 할떄에는 문자열이 되겠습니다.

source_char는 표현식이 되겠습니다. 찾고자하는 문자열 또는 위에서 말했던 검색표현식을 써서 정의를 내리는 부분입니다.

position은 검색 시작위치로 어디서부터 검색을 할 것인지에 대해 정의를 내리는 부분입니다.

occurrence는 찾는 패턴의 순번으로 몇번째를 검색할 것인지를 알아보는 것이구요

return_opt는 0, 1로 구성이 되어져 있고 0의 경우 검색한것의 위치, 1의 경우는 검색한것의 반대 위치를 말합니다.

match_param은 문자열의 옵션인데요. 예를들어 i의 경우 대소문자 구분안하고 출력하는 것을 말하는 것입니다.

이외에도 c, n, m, x가 있습니다. c는 대소문자 구분, n은 점을 허용, m은 여러줄일경우, x는 공백 무시 라는 기능을 합니다.

subexpr은 패턴과 함께쓰이는 보조표현식으로 쓰이는 것인데 주로 쓰이진 않습니다.

 

문법을 살펴보니 역시.. 만만치 않은게 사실이죠 ㅎ 하지만 알아두면 열번고생할거 한번만 고생하면 된다는게 지식의 힘!!ㅎ

 

 

 

offset과 문자열 여러가지를 한번 살펴보는 예제입니다. 이 예제는 대문자가 연달아 세개가 붙어있는 것의 위치를 알아보라고 한 예입니다.

보시다 싶이 첫번째 레코드와 두번째 레코드에서 ABC123 ABC 123 이렇게 두개가 있죠?

여기서 보셔야 할건 offset입니다. 아까 말했듯이 0은 제대로 된 결과 1은 반대의 결과라고 했습니다.

offset이 1인 경우를 보면 4라고 나와있죠? 연속되는 대문자 ABC 다음의 순서인 4가 출력되는 것을 알 수 있습니다.

그리고 네번쨰 컬럼의 값을 보면 match_param인 i를 붙여서 대소문자 구분을 없앴는데요. 그래서 값이 저렇게 나옴을 알 수 있습니다!

 

여기까지 REGEXP_INSTR를 살펴보았습니다. 확실히 정규식이 복잡하고 어려움을 느끼네요.. 이제 앞으로 두개!

 

REGEXP_INSTR가 나왔으니 이번에는 SUBSTR를 해야겠죠 ㅎ

REGEXP_SUBSTR도 특정패턴에서 주어진 문자를 추출해 내는 역할입니다.

예를 한번 볼까요?

 

 

첫번째 예입니다. [^ ]은 공백이 아닌 이라는 뜻이죠? 거기에 +[def]인 문자열이랬으니까 공백이 아닌것부터 def인 *def가 출력값으로 나오게 되는 것임을 알 수 있습니다 ㅎ 다른 예를 또 봐서 이해를 높여보죠 ㅎ

이 예제에 관해서는 저 자체도 고민을 많이 했고 실험도 많이 한 그런 예제입니다.

문제는 교수들이 가지고 있는 홈페이지. 즉 professor 테이블의 hpage 중 http://를 제외하고 출력하라 라는 문제입니다.

 

 

원래는 /를 LTRIM으로 없애야 하지만 설명을 위해서 우선 두었습니다 ㅎ 왜 고민을 했는지 연구를 했는지에 대한건 문자열을 봐주세요.

다소 좀 복잡하게 되어 있습니다. '/([[:alnum:]]+\.?){3,4}?') 이라고 표현식이 되어있습니다.

도대체 뭘 알고싶어하는거지? 라는 의문이 드실겁니다. http://뒤에 있는 것들을 추출하고 싶어서 이러한 표현식을 쓴겁니다.

하나하나 알아보도록 하겠습니다.[[:alnum:]]은 알파벳+숫자를 의미하는 것으로 php에서 쓰이는 것으로 알고 있는데 여기서도 쓰이네요

거기에 +로 알파벳 숫자뒤에 또 뭔가가 오는데 escape .(점) ?(물음표) 가 되어 있죠. 이제 이건 뭐지? 싶습니다 ㅎ

점은 특수문자이므로 \표시로 탈출시켜서 점이 있는 문자열임을 알려주고 물음표를 이용하여 점이 여러개임을 알려줍니다. 친절하네요

이제 그 뒤에 {3, 4}라는 표시가 또 있습니다. 앞에 괄호가 있는 것으로 봐선 abc. 이렇게 되어있는 세트를 가리키는 것을 알 수 있습니다

3과 4는 세트의 최소치와 최대치라고 생각하시면 됩니다. 그래서 최소치인 3으로 맞춰져서 위같은 결과값이 나오는 겁니다.

아래에 비교한 것을 보여드릴께요.

 

 

이것의 경우 {4,4} 최대치와 최소치가 같습니다. 그랬더니 허은의 홈페이지가 kr까지 출력이 되는 것을 알 수 있습니다.

저도 최대치가 왜 맞춰지고 그런건지는 잘 모르겠네요..;; 그냥 최소치만 쓰면되지 뭘.. 이란 생각으로 아래와 같은 실험을 또 ㅎㅎ

 

이번엔 최소 최대의 개념이 아닌 그냥 숫자 하나만을 썼습니다. 그냥 4을 썼더니 그냥 나오네요.. 뭔차이니 너..

 

이것도 맨처음 최소치였던 3을 했더니 이렇게.. 요로코롬 나오네요.. 참 그렇네요 ㅎ 최대는 왜쓰는거지..

 

이번엔 2를 써봤습니다. 그랬더니 net과 com이 나왔던 것들이 사라지고 두번째 세트까지만 나오네요. 대충 감이 오시나요?ㅎ

저도 이렇게 테스트를 해보고 나서야 아~ 이거 이렇게 쓰는 거구나 라고 생각하게 됩니다 ㅎ 역시 스스로학습!!ㅎ

 

이제 정규식의 마지막 REGEXP_COUNT를 설명하겠습니다. 특정문자의 개수를 세는 함수인데요. 예제를 보죠 ㅎ

 

 

 

이게 바로 COUNT입니다. 과연 카운트 같은 역할을 하고 있죠?ㅎ 숫자세기 ㅎ 적절한데 쓰면 아주 유용하겠죠?ㅎ

 

COUNT또한 match_param과 position을 모두 쓸 수 있습니다.

REGEXP_COUNT(source_char, pattern, position, match_param) 원래는 이렇게 되는것이죠

정규식의 모든 기능을 쓸 수 있다는 것! 알아두시기 바랍니다.

 

정규식의 모든것이 끝났습니다. 이거 다쓰는데 한달이나 걸렸네요.. 최초 비공개로 올렸을때는 7월이었는데 8월 말일이 됐으니까요 ㅎ

그동안 생각도 많이하고 놀러도 많이 다니고 했지만 마음 다잡고 다시 공부 고고씽 ㅎ

내일은 숫자함수로 돌아오겠습니다. 숫자함수는 문자함수보다는 간단간단합니다 ㅎ 날짜 함수도 다룰 수 있겠네요 ㅎ 분량안나오면 ㅎ