문제7. SQL / 데이터 정제(문장부호 제거)

[코드를 쓴 상황]

텍스트를 어절별로 나누어 키워드를 분석하시오

 

 

[코드 작성]

1. speech테이블의 텍스트(speech_text)를 regexp_substr 함수를 이용하여 어절로 나눈다. 이때 텍스트를 소문자로 바꿔준다(어절별로 나눈 뒤에 바꿔줘도 상관없음)

select regexp_substr(lower(speech_text),'[^ ]+',1,n) as word
 from speech, ( select level as n
                  from dual
                  connect by level <= 100) ;

2. [문장부호 제거] 우선 trim 혹은 replace를 이용해 큰 따옴표(")를 제거

trim 사용할 경우에는 큰 따옴표를 제거하는 과정에서 단어 앞뒤로 생성된 공백도 제거되지만, replace는 공백을 제거하진 못함.

(영어텍스트에서 작은따옴표 제거시에는 주의가 필요. 단어 사이에 들어있는 어퍼스트로피(')를 제거할 수 있으므로 우선 이 문제에서 작은따옴표 제거는 생략함)

-- trim 사용
select trim('"' from regexp_substr(lower(speech_text),'[^ ]+',1,n)) as word
 from speech, ( select level as n
                  from dual
                  connect by level <= 100) ;


-- replace 사용
select replace( regexp_substr(lower(speech_text),'[^ ]+',1,n),'"','') as word
 from speech, ( select level as n
                 from dual
                 connect by level <= 100) ;

 

3. [문장부호 제거] rtrim을 이용해 단어 끝에 달린 온점(.), 반점(,), 콜론(:), 세미콜론(;), 느낌표(!), 물음표(?)를 모두 제거함.

select rtrim(trim('"' from regexp_substr(lower(speech_text),'[^ ]+',1,n)),'.,;:!?') as word
 from speech, ( select level as n
                  from dual
                  connect by level <= 100) ;

4. null을 제거하기 위해 위의 쿼리를 서브쿼리로 넣음

select *
 from ( select rtrim(trim('"' from regexp_substr(lower(speech_text),'[^ ]+',1,n)),'.,;:!?') as word
          from speech, ( select level as n
                          from dual
                          connect by level <= 100)  )
 where word is not null ;

 

 

[유의사항]

1. 영어텍스트와 한글텍스트를 정제하는데 차이가 있음. 한글텍스트의 경우 작은따옴표(')까지 제거해야 하지만, 영어텍스트는 작은따옴표(')까지 제거시 단어의 의미가 변할 수 있음

2. 한글텍스트에서는 따옴표가 단어사이에 들어가 의미를 바꾸지 않으므로 작은따옴표 제거시 trim 대신 replace를 이용해도 됨. 

select trim('"' from replace(regexp_substr('한글 텍스트에서는 ''따옴표''가 강조의 의미로 주로 사용됩니다. 
                               단어의 의미에 영향을 주지 "않습니다."','[^ ]+',1,n),'''',''))
 from dual, ( select level as n
                from dual
                connect by level <= 20 );

3. chr()을 이용해서 캐리지라인(chr(10))과 라인피드(chr(13))도 제거해야함

예를들어 줄바꿈(엔터)을 제거할 경우 replace 함수를 이용해 데이터 값(chr(10))을 null로 변환해줌.

select replace(regexp_substr('한글 텍스트에서는 ''따옴표''가 강조의 의미로 주로 사용됩니다. 
                               단어의 의미에 영향을 주지 "않습니다."','[^ ]+',1,n), chr(10) ,'') as word
 from dual, ( select level as n
                from dual
                connect by level <= 20 ) ;

+) chr(숫자)는 아스키코드를 얻기 위한 함수로 주로 사용하는 숫자는 다음과 같음.

chr(9)	탭(Tab)
chr(10)	라인피드(줄바꿈, 엔터), 현재 커서가 다음행으로 이동
chr(13)	캐리지리턴 (행의 처음으로), 개행문자
chr(38)	&
chr(39)	작은 따옴표(')
chr(44)	쉼표(,)