WeLoveDoctor

일정한 패턴을 가진 항목 찾기

Written by

byun1114

in

쉽게 말하면 ‘> 125 (150)’ 이런 식으로 구성되어 있는 것을 찾는 것이다. 

WHERE 변수 LIKE '%>%(%)%'

저런 형식으로 입력해서 찾으면 된다.

그 중에서 괄호 안의 값을 찾는 방법이 필요했다. 내가 시도한 방법은 ‘(‘를 기준으로 문자열을 분리 후 2번째 항목을 선택한다. 그럼 ‘150)’ 형식으로 된다. 그 다음에는 ‘)’을 ”으로 REPLACE 시켜준다. 

REPLACE(split_part(변수, '(', 2), ')', '')

일부가 3.56E-1 이런 형식을 하고 있었다. 불행 중 다행이라면 여백이 없었다는 것이다. 다음과 같이 실수형태로 변환시켰다.

CAST(SPLIT_PART(변수, 'E', 1) AS NUMERIC)*10^CAST(SPLIT_PART(변수, 'E', 2)

127.7. 과 같은 형식도 있었다. PostgreSQL에서는 뒤쪽에서 부터 단어를 선택하는 함수는 없는 것 같다. 하지만, 뒤집는 것은 있다. 일단 해당 값에서 ‘.’이 2개가 있는 것을 확인하는 것이 필요하다. 전체 길이에서 찾고자 하는 글자를 삭제한 후 글자 길이가 2개만큼 줄어드는 것을 찾으면 된다.

WHERE (LENGTH(변수)-LENGTH(REPLACE(변수, '.', ''))) = 2

‘.’이 2개인 것을 찾았으면 뒤집는다. 그럼 ‘.7.721’이다. 2번째 글자부터 끝까지 단어를 선택한 다음 다시 뒤집으면 된다.

REVERSE(SUBSTR(REVERSE(변수), 2, CHAR_LENGTH(변수)))

여백을 없애야 했다. 여백은 TRIM으로 없애는 것이 기본이나 별의 별 형식으로 공간을 잡아먹는 녀석들이 있다. 따라서 정규식이나 escape 문자를 이용한다.

REGEXP_REPLACE(변수, '\s+', '')

이것은 정규식 형태이다. 빈 줄은 다음과 같은 식으로 제거한다.

REGEXP_REPLACE(변수, E'[\n\r]+', '')

이렇게 실수형태가 아닌 것들을 다 제외시킨다. 나의 경우에는 이제 실수형태가 아닌 것들만 남아 있었다. 이를 확인하는 것은 매우 어려웠다. 이 정규식을 인터넷에 공개해주신 분(http://mwultong.blogspot.com/2006/09/regex-signed-float.html)께 대단히 감사. 추가로 자료를 찾아본 결과 정규식 표현에서 ~는 like 의미로 쓰인다고 한다. 그렇다고 like를 쓰면 동작하지는 않는다.

WHERE 변수 ~ '^[+-]?\d*(\.?\d*)$'

이렇게 하면 실수 형태인 자료들을 찾을 수 있다. 그런데 이런 값들은 정리해줄 필요가 없다.

select regexp_replace(변수, '^\D+', '') from ... 
  where 변수 ~ '^\D+[+-]?\d*(\.?\d*)$';

이렇게 하면 변수에 실수 앞 부분에 숫자 이외의 문자가 하나 이상 포함되어 있고 그 뒷 부분은 실수형태인 것들을 찾을 수 있다. 대표적이라면 > 10 같은 것이다. 이러면 실수형태 앞부분을 삭제할 수 있다.

select regexp_replace(변수, '\D+$', '') from ... 
  where 변수 ~ '^[+-]?\d*(\.?\d*)\D+$';

이러면 실수형태 숫자 뒤쪽에 문자가 있는 것들을 삭제할 수 있다. ’20 이하’ 같은 것 말이다.

  • 여기까지 해보니 다음과 같은 순서대로 하면 될 것 같다. >100(125) 같은 형태의 자료는 우선적으로 처리해 준다.
  • 쉼표를 제거한다.
  • ‘..’을 처리한다.
  • 공백과 빈 줄을 제거한다.
  • 빈 칸이 입력된 것은 NULL로 바꾸어 준다.
  • 앞에 숫자 아닌 거 들어간 것을 없애주고, 뒤에 숫자 아닌거 들어간 것을 없애준다.
  • 실수 형태가 아닌 자료들을 확인해서 NULL로 바꾼다.
UPDATE .. SET 변수 = REGEXP_REPLACE(변수, '^\D+', '') ;
UPDATE .. SET 변수 = REGEXP_REPLACE(변수, '\D+$', '') ;

More posts