사이드바 영역으로 건너뛰기

[펌] 오라클 컬럼의 값이 문자인지 숫자인지 구별하는 함수

출처 : http://oracleebs.co.kr/?p=41

 

 

STX_OUT_TEMP 테이블의 ATTRIBUTE1 컬럼의 값들이 아래 그림에서와 같습니다.

2010-03-23_155814

즉, VARCHAR2 형식으로 정의된 컬럼에 저장된 값은 숫자형, 문자형, 기호 이렇게 아무렇게나 저장이 되어 있습니다.
이 컬럼의 값들 중에 숫자형식으로 나타낼 수 있는 값들만을 쿼리하고 싶다면,

		SELECT ATTRIBUTE1
  FROM STX_OUT_TEMP
 WHERE TRANSLATE(ATTRIBUTE1,'A1234567890','A') IS NULL;

과 같은 SQL 문을 생각할 수 있습니다.

TRANSLATE() 함수를 이용하여 숫자 혹은 문자를 특정문자(위의 SQL에서는 NULL로 바뀜)로 변환하는 방법입니다. TRANSLATE() 함수에 대한 설명은 레퍼런스를 찾아보시면 될 듯 하구요. 간단히 예를 들어

		SELECT TRANSLATE(ATTRIBUTE1,'A1234567890','A'),                                                    -- 숫자
       TRANSLATE(ATTRIBUTE1,'0abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','0'),          -- 문자
       TRANSLATE(ATTRIBUTE1,'@1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','@') -- 특수문자
  FROM STX_OUT_TEMP;

위의 SQL을 실행해보면, TRANSLATE() 함수로 문자 혹은 숫자 중 골라내고 싶은 값을 NULL 문자로 치환하여 WHERE 문에 IS NULL 을 조건으로 주면 해당 RECORD를 SELECT 할 수 있습니다.

다시 말해, 컬럼의 값들이 숫자로만 되어 있는 지, 아니면 문자로 되어있는지 이렇게 알고 싶을 때가 있는데 이때 쉽게 사용할 수 있는 함수가 ORACLE 에는 없는 것으로 알고 있습니다. 그래서 위와 같은 SQL문을 생각하게 되는 것입니다.

그러나, MS-SQL 에서는 ISNUMERIC() 이라는 함수가 있습니다.  
MS-SQL의 이 함수처럼 ORACLE 에서도 테이블의 특정 컬럼에 문자와 숫자가 병행되어 입력되는 상황에서 이 컬럼중에서 숫자 형식으로만 된 값과 아니면 문자(정확히 말하면, 숫자로 변환할 수 없는)값으로 된 레코드만을 알고자 할 때가 필요합니다.

그래서, 오라클에서도 MS-SQL에서와 동일한 기능을 하는 함수를 만들어 사용하면 될 듯 하여 아래와 같은 함수를 만들었습니다.

		CREATE OR REPLACE FUNCTION ISNUMERIC(i_str VARCHAR2) RETURN NUMBER
IS
 v_ret NUMBER;
BEGIN
  IF i_str IS NULL OR LENGTH(TRIM(i_str)) = 0 THEN
    RETURN 0;
  END IF;
 
  V_RET := TO_NUMBER(I_STR);
  RETURN 1;
 
EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END ;

함수의 내용을 보면 입력 값이 올바른 숫자 데이터 형식으로 변환되면 1을 반환하고, 그렇지 않으면 0을 반환합니다.

이렇게 작성된 함수를 활용하여, 처음 소개한 SQL 문을 변경하여 보면

		SELECT ATTRIBUTE1
  FROM STX_OUT_TEMP
 WHERE ISNUMERIC(ATTRIBUTE1) = 1;

로 변경할 수 있습니다.

이와 같이 공통적으로 활용될 수 있는 함수는 미리 정의하여 프로젝트 팀원간에 공용으로 활용하면 좋을 듯 합니다.

 

작성 : amagrammer / 편집 : amagrammer

진보블로그 공감 버튼트위터로 리트윗하기페이스북에 공유하기딜리셔스에 북마크