본문
출처 : http://oracleebs.co.kr/?p=41
STX_OUT_TEMP 테이블의 ATTRIBUTE1 컬럼의 값들이 아래 그림에서와 같습니다.
즉, 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
댓글 목록
앙겔부처
관리 메뉴
본문
헐... 건강하세요...;; 아픈 것보다 병원비 얘기가 더 무섭네요....-_-부가 정보