오라클에서의 정규식 활용

1. 문제

오라클 DB를 사용하던 중 다음과 같은 상황이 발생했다.

데이터를 화면으로 내보낼 때 유효숫자^[각주:1]에 반올림이 있어야 한다.

예를 들면, 0.000001247을 유효숫자 3자리에서 반올림하면 0.00000125가 된다.

2. 해결 방법

먼저 0.000001247에 적절한 수를 곱하여 0.1247을 만든다.
그리고 ROUND 함수를 사용하여 0.125를 만든 뒤 다시 그 수로 나누어 0.00000125를 구하면 된다.
이때, 0.000001247은 1.247E-6으로 표현할 수 있다. 그러므로 1E-5이 "적절한 수"이다.
단, 절대값이 1보다 큰 숫자는 위와 같은 변환을 거칠 필요가 없이 바로 ROUND 함수로 처리할 수 있다.

아래는 오라클에서의 query이다.

SELECT T.N AS N
     , ROUND( T.N / POW, 3 ) * POW AS ROUND_N
  FROM 
     (
	   SELECT T.N AS N
	        , CASE WHEN 1 <= ABS( T.N ) THEN 1
			       WHEN TO_NUMBER( '1'||REGEXP_SUBSTR( TO_CHAR( T.N, '9.9EEEE' ), 'E-[0-9]*' ) ) * 10 AS POW
		 FROM 
		    (
			  SELECT 0.000001247 AS N FROM DUAL
			  UNION ALL SELECT 165 AS N FROM DUAL
			  UNION ALL SELECT 0.00135 AS N FROM DUAL
			)
	 )

REGEXP_SUBSTR는 SUBSTR의 확장판으로, 정규식에 해당하는 문자열을 반환한다.^[각주:2]
'E-[0-9]*'는 문자열에서 E-다음 숫자가 나오는 부분을 잘라내라는 의미이다.
예를 들면, '1.234E-112'에서는 'E-112'가 선택된다.

3. 마치며

정규식을 사용하지 않아도 위의 문제를 해결할 수 있다. 다만, query가 약간 더 길어질 뿐이다.
정규식을 배울 때에는 실제로 쓸 일은 없을 것이라 생각했지만 예상이 빗나가서 즐거웠다.
언젠가 교과서에서나 볼법한 복잡한 정규식을 써볼 기회도 있었으면 한다.

---

1. 수의 정확도에 영향을 주는 숫자. 자세한 사항은 위키백과를 참고하자. [본문으로]
2. 단, 오라클 10g 이상에서 지원한다. [본문으로]