DB별 TO_NUMBER (변환함수) > 기술자료

• 목록

지수타입 데이타와 숫자 데이타가 혼재된 경우 숫자형으로 모두 변경해서 가져오는 방법입니다

컬럼 타입 : varchar(50)
아래 2개의 데이타가 저장된 디비테이블

202208080919012
2.0220808091901E+14    <<== 지수( exponential numbers ) 타입


1. Oracle / Tibero
TO_NUMBER('10')
select TO_NUMBER(디비컬럼) from 디비테이블
select TO_CHAR(TO_NUMBER(디비컬럼, 'FM9.99EEEE')) from 디비테이블

select to_char(77052512125510000,'FM9.99EEEE') char_,
        to_number('7.71E+16', 'FM9.99EEEE') number_,
        to_char(to_number('7.71E+16', 'FM9.99EEEE')) char_2
from dual;

CHAR_           NUMBER_         CHAR_2                                  
-------------    -----------------    ----------------------------------------
7.71E+16      7.7100E+16      77100000000000000                       
1 row selected.

참고자료
https://community.oracle.com/tech/developers/discussion/2395944/how-to-convert-the-exponential-data-into-number



2. MSSQL
CONVERT(NUMERIC, '10')
select CONVERT(NUMERIC, 디비컬럼) from 디비테이블
202208080919012
202208080919010

CONVERT(VARCHAR(38), CAST(디비컬럼 AS DECIMAL(38, 0)))
select CONVERT(VARCHAR(38), CAST(디비컬럼 AS DECIMAL(38, 0))) from 디비테이블
202208080919012
202208080919010

참고자료
https://www.codeproject.com/Questions/732540/how-to-import-Exponential-and-non-Exponential-data



3. MySQL
CAST('10' AS UNSIGNED)
select CAST(디비컬럼 AS UNSIGNED) from 디비테이블
202208080919012
2

참고자료
https://extremeblue.tistory.com/35



4. Informix
TO_NUMBER('10')
select TO_NUMBER(디비컬럼) from 디비테이블



5. Sybase
CAST('10' AS INT)
select CAST(barcode AS INT) from 디비테이블



6. DB2
CAST('10' AS INTEGER)
select TO_NUMBER(barcode) from 디비테이블