[Sql] Oracle - 숫자여부 체크

출처 : http://cafe.naver.com/litave.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=279


오라클에서 기존의 legacy 테이블을 조인하거나 혹은 쿼리에 대한 튜닝을 하다보면

A 테이블의 개념상 FK인 칼럼은  B 테이블의 Object_ID 와 같다라는 아래와 같은 문장을 쓰게 되는데

TB_A.person = TB_USER.object_id

문제는 위의 person이라는 칼럼이 varchar2 타입이고, object_id 칼럼은 number 타입인 경우가 있습니다.

이 경우 일반적으로는 person 이란 곳에도 당연히 TB_USER 의 object_id 와 매칭되도록 숫자형식의 값만

person 칼럼에 들어가는 것이 당연하니까 별 문제없이 디비에서 알아서 잘 캐스팅해서 처리해줍니다.

하지만, 실제 현장의 legacy에는 person 이란 곳에 숫자외의 정말로 문자값도 들어가는 케이스가 많습니다.

이런 경우 오라클은 여지없이 아래와 같은 에러를 내보내죠.

ORA-01722: invalid number

이런 경우에 생각해보는게 TB_A의 칼럼중에 숫자인 칼럼만 TB_USER와 비교하게 할 순 없을까? 즉 값이

숫자인 것만 찾는 방법은 없을까? 에 대해 고민하다가 아래와 같은 쿼리를 찾게 되었습니다.

/* 숫자인 값만 */

SELECT * FROM TB_A
WHERE TRIM(TRANSLATE(person, '1234567890', '          ')) IS NULL

/* 숫자가 아닌 문자 값만 */

SELECT * FROM TB_A
WHERE TRIM(TRANSLATE(person, '1234567890', '          ')) IS NOT NULL

/* 위의 조인에 대한 적용 예제 */

SELECT A.* FROM TB_A A, TB_USER B
WHERE A.person = B.object_id

AND A.TRIM(TRANSLATE(A.person, '1234567890', '          ')) IS NOT NULL

'          ' 의 사이에 공백은 10칸을 채워주셔야합니다.

원리는 단순합니다. 칼럼의 값중 숫자에 해당되는 값을 공백으로 변환시키고 트림처리한뒤에

널이냐 아니냐로 체크하는 것입니다.



--------------------------------------------------------------------------------------

다른 방안

http://www.arikaplan.com/oracle/ari021000.html

Don't you think Oracle would have made ISNUMBER, ISDATE, etc.? Other databases
have these functions.

Anyway, here is a "ISNUMBER" function you can use:

select instr(translate(column_name,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
	'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM table_name;

It returns 0 if it is a number, 1 if it is not.

This works if the field has only characters or digits. For example, having !!!
as a string would not work in this case. If you care about other characters,
change the translate to add those characters.

-Ari
____________________________________________________________________________________
Date: Tue, 13 Nov 2001 17:46:42 -0500
From: VIJAY JAGDALE 
To: ari@pocketdba.com
Subject: improved ISNUMBER function (tip 403)

Hi Ari,

I had contributed to one of your earlier tips(#255). Here is another better way
of doing tip #403 (ISNUMBER)

select length(translate(trim(column_name),' +-.0123456789',' ')) from dual

will give you a zero if it is a number or greater than zero if not numeric
(actually gives the count of non numeric characters)

NOTE THERE IS A SPACE BEFORE THE PLUS in the second parameter of the translate,
and a single space inthe last option.

This method has the advantege that it does not care what other characters you
have in the field... you can even have unprintable characters.

The additional TRIM functions gets rid leading or trailing spaces.

Also note that if you want the accountants representation of negative numbers,
you can also add parenthesis to the second parameter.

regards,

Vijay Jagdale
Technical Systems Specialist
Michigan Department of Transportation
jagdalev@mdot.state.mi.us


Back to Ari Kaplan's Home Page

ck to Ari Kaplan's Home Page







-------------------------------------------------------------------

활용

SCORE3 이 문자인 0~9라면 소수점 형태로 나타냄(3.0), 소수점이 포함 되어잇으면 그냥 SCORE3 그대로 표시한다.

select
    DECODE(SCORE3, '', '0.0',
                      CASE WHEN TRIM(TRANSLATE(SCORE3,'1234567890','          ')) IS NULL
                               THEN TO_CHAR(SCORE3,'FM990.09') 
                               ELSE SCORE3 END
                ) AS ClunmA
from
  (SELECT '3' SCORE3 FROM   DUAL);