데이터 타입 변환
Tibero에서 비교, 연산등을 위한 SQL 데이터 타입 변환 방법을 설명합니다.
데이터 타입은 사용자가 명시적으로 할 수도 있고, 필요에 의하여 암시적으로 이루어지기도 합니다.
명시적 타입 변환
사용자가 SQL 변환 함수를 직접 사용하여 타입을 변환할 수 있습니다.
다음은 타입 변환 함수목록입니다(행은 변환 전 타입, 열은 변환 후 타입입니다).
[표 1] 명시적 타입 변환 (1)
CHAR, VARCHAR2, NCHAR, NVARCHAR2
TO_CHAR, TO_NCHAR
TO_NUMBER
TO_DATE, TO_TIME, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, TO_YMINTERVAL
NUMBER
TO_CHAR, TO_NCHAR
-
NUMTOYMINTERVAL, NUMTODSINTERVAL
날짜, 시간 및 인터벌
TO_CHAR, TO_NCHAR
X
-
RAW
RAWTOHEX
X
X
ROWID
ROWIDTOCHAR
X
X
LONG, LONG RAW
LONG_TO_CHAR
X
X
CLOB, NCLOB, BLOB
TO_CHAR, TO_NCHAR
X
X
BINARY_FLOAT
TO_CHAR, TO_NCHAR
TO_NUMBER
X
BINARY_DOUBLE
TO_CHAR, TO_NCHAR
TO_NUMBER
X
JSON
TO_CHAR
X
X
[표 2] 명시적 타입 변환 (2)
CHAR, VARCHAR2, NCHAR,
TO_BINARY_DOU BLE
HEXTORAW
CHARTOROWID
LONG_TO_CHAR
NVARCHAR2
NUMBER
TO_BINARY_DOUBLE
X
X
X
날짜, 시간 및 인터벌
X
X
X
X
RAW
-
-
X
X
ROWID
X
X
-
X
LONG, LONG RAW
X
X
X
-
CLOB, NCLOB, BLOB
X
X
X
X
BINARY_FLOAT
X
X
X
X
BINARY_DOUBLE
X
X
X
X
JSON
X
X
X
X
[표 3] 명시적 타입 변환 (3)
CHAR, VARCHAR2, NCHAR, NVARCHAR2
TO_CLOB
TO_BINARY_FLOAT
TO_BINARY_DOUBLE
X
NUMBER
X
TO_BINARY_FLOAT
TO_BINARY_DOUBLE
X
날짜, 시간 및 인터벌
X
X
X
X
RAW
TO_BLOB
X
X
X
ROWID
X
X
X
X
LONG, LONG RAW
TO_LOB
X
X
X
CLOB, NCLOB, BLOB
TO_CLOB
X
X
X
BINARY_FLOAT
X
TO_BINARY_FLOAT
TO_BINARY_DOUBLE
X
BINARY_DOUBLE
X
TO_BINARY_FLOAT
TO_BINARY_DOUBLE
X
JSON
X
X
X
-
암시적 타입 변환
사용자가 명시적으로 타입을 변환하지 않더라도, 필요하다면 암시적으로 타입을 변환하여 줍니다.
암시적 타입 변환이 필요한 경우는 아래와 같습니다.
컬럼에 다른 타입의 데이터를 INSERT, UPDATE하는 경우
조건문에서 비교하는 양쪽 값이 다른 타입인 경우
다음은 암시적 타입 변환 관계입니다(행은 변환 전 타입, 열은 변환 후 타입입니다).
[표 4] 암시적 타입 변환 (1)
NUMBER
-
O
O
X
X
X
X
CHAR
O
-
O
O
O
O
O
VARCHAR2
O
O
-
O
O
O
O
RAW
X
O
O
-
X
X
X
DATE
X
O
O
X
-
X
O
TIME
X
O
O
X
X
-
X
TIMESTAMP
X
O
O
X
O
X
-
INTERVAL YEAR TO MONTH
X
O
O
X
X
X
X
INTERVAL DAY TO SECOND
X
O
O
X
X
X
X
LONG
X
X
X
X
X
X
X
LONG RAW
X
X
X
X
X
X
X
BLOB
X
X
X
O
X
X
X
CLOB
X
O
O
X
X
X
X
ROWID
X
O
O
X
X
X
X
NCHAR
O
O
O
O
O
O
O
NVARCHAR2
O
O
O
O
O
O
O
NCLOB
X
O
O
X
X
X
X
TIMESTAMP WITH TIME ZONE
X
O
O
X
O
X
O
TIMESTAMP WITH LOCAL TIMEZONE
X
O
O
X
O
X
O
BINARY_FLOAT
O
O
O
X
X
X
X
BINARY_DOUBLE
O
O
O
X
X
X
X
JSON
X
O
O
X
X
X
X
[표 5] 암시적 타입 변환 (2)
NUMBER
X
X
O
X
X
O
X
CHAR
O
O
O
O
O
O
O
VARCHAR2
O
O
O
O
O
O
O
RAW
X
X
O
O
O
O
X
DATE
X
X
X
X
X
X
X
TIME
X
X
X
X
X
X
X
TIMESTAMP
X
X
O
X
X
X
X
INTERVAL YEAR TO MONTH
-
X
O
X
X
X
X
INTERVAL DAY TO SECOND
X
-
O
X
X
X
X
LONG
X
X
-
X
X
X
X
LONG RAW
X
X
X
-
X
X
X
BLOB
X
X
O
X
-
X
X
CLOB
X
X
O
X
X
-
X
ROWID
X
X
O
X
X
X
-
NCHAR
O
O
O
O
X
O
O
NVARCHAR2
O
O
O
O
X
O
O
NCLOB
X
X
O
X
X
O
X
TIMESTAMP WITH TIMEZONE
X
X
O
X
X
X
X
TIMESTAMP WITH LOCAL TZ
X
X
O
X
X
X
X
BINARY_FLOAT
X
X
O
X
X
X
X
BINARY_DOUBLE
X
X
O
X
X
X
X
JSON
X
X
X
O
X
O
X
[표 6] 암시적 타입 변환 (3)
NUMBER
O
O
X
X
X
O
O
X
CHAR
O
O
O
O
O
O
O
O
VARCHAR2
O
O
O
O
O
O
O
O
RAW
O
O
X
X
X
X
X
X
DATE
O
O
X
O
O
X
X
X
TIME
O
O
X
X
X
X
X
X
TIMESTAMP
O
O
X
O
O
X
X
X
INTERVAL YEAR TO MONTH
O
O
X
X
X
X
X
X
INTERVAL DAY TO SECOND
O
O
X
X
X
X
X
X
LONG
X
X
X
X
X
X
X
X
LONG RAW
X
X
X
X
X
X
X
X
BLOB
X
X
X
X
X
X
X
O
CLOB
O
O
X
O
O
X
X
O
ROWID
O
O
X
X
X
X
X
X
NCHAR
-
O
O
O
O
O
O
O
NVARCHAR2
O
-
O
O
O
O
O
O
NCLOB
O
O
-
X
X
X
X
O
TIMESTAMP WITH TZ
O
O
X
-
O
X
X
X
TIMESTAMP WITH LOCAL TZ
O
O
X
O
-
X
X
X
BINARY_FLOAT
O
O
X
X
X
-
O
X
BINARY_DOUBLE
O
O
X
X
X
O
-
X
JSON
X
X
X
X
X
X
X
-
타입비교
다음은 두 값에 대한 타입을 비교할 때 어느쪽 타입으로 맞춰서 비교하는지를 나타내는 표입니다.
[표 7] 타입비교 (1)
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2
CHAR
NUMBER
CHAR
VARCHAR2
CHAR
VARCHAR2
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
RAW
VARCHAR2
CHAR
VARCHAR2
RAW
DATE
X
DATE
DATE
VARCHAR2
TIME
X
TIME
TIME
VARCHAR2
TIMESTAMP
VARCHAR2
TIMESTAMP
TIMESTAMP
VARCHAR2
INTERVAL YEAR TO MONTH
VARCHAR2
INTERVAL Y TO MONTH
INTERVAL Y TO MONTH
VARCHAR2
INTERVAL DAY TO SECOND
VARCHAR2
INTERVAL D TO SEC
INTERVAL D TO SEC
VARCHAR2
LONG
X
LONG
LONG
VARCHAR2
LONG RAW
VARCHAR2
LONG
LONG
VARCHAR2
BLOB
X
X
X
X
CLOB
X
CLOB
CLOB
CLOB
ROWID
VARCHAR2
ROWID
ROWID
VARCHAR2
NCHAR
NUMBER
NCHAR
NVARCHAR2
NCHAR
NVARCHAR2
NUMBER
NVARCHAR2
NVARCHAR2
NVARCHAR2
NCLOB
X
NCLOB
NCLOB
NCLOB
TIMESTAMP WITH TIME ZONE
VARCHAR2
TIMESTAMP
TIMESTAMP
VARCHAR2
TIMESTAMP WITH LOCAL TIME ZONE
VARCHAR2
TIMESTAMP
TIMESTAMP
VARCHAR2
BINARY_FLOAT
BINARY_FLOAT
BINARY_FLOAT
BINARY_FLOAT
VARCHAR2
BINARY_DOUBLE
BINARY_DOUBLE
BINARY_DOUBLE
BINARY_DOUBLE
VARCHAR2
JSON
X
X
X
X
[표 8] 타입비교 (2)
NUMBER
X
X
VARCHAR2
VARCHAR2
CHAR
DATE
TIME
TIMESTAMP
INTERVAL YEAR TO MONTH
VARCHAR2
DATE
TIME
TIMESTAMP
INTERVAL YEAR TO MONTH
RAW
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
DATE
DATE
VARCHAR2
TIMESTAMP
VARCHAR2
TIME
VARCHAR2
TIME
VARCHAR2
VARCHAR2
TIMESTAMP
TIMESTAMP
VARCHAR2
TIMESTAMP
VARCHAR2
INTERVAL YEAR TO MONTH
VARCHAR2
VARCHAR2
VARCHAR2
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
VARCHAR2
VARCHAR2
VARCHAR2
INTERVAL DAY TO SECOND
LONG
X
X
X
X
LONG RAW
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
BLOB
X
X
X
X
CLOB
X
X
X
X
ROWID
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
NCHAR
DATE
TIME
TIMESTAMP
INTERVAL YEAR TO MONTH
NVARCHAR2
DATE
TIME
TIMESTAMP
INTERVAL YEAR TO MONTH
NCLOB
X
X
X
X
TIMESTAMP WITH TIME ZONE
TIMESTAMP
TIMESTAMP
TIMESTAMP WITH TIME ZONE
VARCHAR2
TIMESTAMP WITH LOCAL TZ
TIMESTAMP
VARCHAR2
TIMESTAMP WITH LOCAL TIMEZONE
VARCHAR2
BINARY_FLOAT
X
X
VARCHAR2
VARCHAR2
BINARY_DOUBLE
X
X
VARCHAR2
VARCHAR2
JSON
X
X
X
X
[표 9] 타입비교 (3)
DATE
VARCHAR2
X
VARCHAR2
X
TIME
VARCHAR2
X
VARCHAR2
X
TIMESTAMP
VARCHAR2
X
VARCHAR2
X
INTERVAL YEAR TO MONTH
VARCHAR2
X
VARCHAR2
X
INTERVAL DAY TO SECOND
VARCHAR2
X
VARCHAR2
X
LONG
X
LONG
LONG
X
LONG RAW
VARCHAR2
LONG
LONG RAW
X
BLOB
X
X
X
BLOB
CLOB
X
LONG
LONG
X
ROWID
VARCHAR2
ROWID
VARCHAR2
X
NCHAR
INTERVAL DAY TO SECOND
X
X
X
NVARCHAR2
INTERVAL DAY TO SECOND
X
X
X
NCLOB
X
X
X
X
TIMESTAMP WITH TIME ZONE
VARCHAR2
X
VARCHAR2
X
TIMESTAMP WITH LOCAL TIME ZONE
VARCHAR2
X
VARCHAR2
X
BINARY_FLOAT
VARCHAR2
X
VARCHAR2
X
BINARY_DOUBLE
VARCHAR2
X
VARCHAR2
X
JSON
X
X
X
X
[표 10] 타입비교 (4)
NUMBER
X
VARCHAR2
NUMBER
NUMBER
CHAR
CLOB
ROWID
NCHAR
NVARCHAR2
VARCHAR2
CLOB
ROWID
NCHAR
NVARCHAR2
RAW
CLOB
VARCHAR2
NCHAR
NVARCHAR2
DATE
X
VARCHAR2
DATE
DATE
TIME
X
VARCHAR2
TIME
TIME
TIMESTAMP
X
VARCHAR2
TIMESTAMP
TIMESTAMP
INTERVAL YEAR TO MONTH
X
VARCHAR2
INTERVAL YEAR TO MONTH
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
X
VARCHAR2
INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND
LONG
LONG
ROWID
X
X
LONG RAW
LONG
VARCHAR2
X
X
BLOB
X
X
X
X
CLOB
CLOB
ROWID
CLOB
CLOB
ROWID
ROWID
ROWID
ROWID
ROWID
NCHAR
CLOB
ROWID
NCHAR
NVARCHAR2
NVARCHAR2
CLOB
ROWID
NCHAR
NVARCHAR2
NCLOB
NCLOB
X
NCLOB
NCLOB
TIMESTAMP WITH TZ
X
VARCHAR2
TIMESTAMP
TIMESTAMP
TIMESTAMP WITH LOCAL TZ
X
VARCHAR2
TIMESTAMP
TIMESTAMP
BINARY_FLOAT
X
VARCHAR2
BINARY_FLOAT
BINARY_FLOAT
BINARY_DOUBLE
X
VARCHAR2
BINARY_DOUBLE
BINARY_DOUBLE
JSON
X
X
X
X
[표 11] 타입비교 (5)
NUMBER
VARCHAR2
BINARY_FLOAT
BINARY_DOUBLE
X
CHAR
TIMESTAMP WITH TZ
TIMESTAMP WITH LOCAL TZ
BINARY_FLOAT
BINARY_DOUBLE
VARCHAR2
TIMESTAMP WITH TZ
TIMESTAMP WITH LOCAL TZ
BINARY_FLOAT
BINARY_DOUBLE
RAW
NCLOB
NCLOB
NCLOB
NCLOB
DATE
TIMESTAMP WITH TZ
TIMESTAMP WITH LOCAL TZ
X
X
TIME
VARCHAR2
VARCHAR2
X
X
TIMESTAMP
TIMESTAMP WITH TZ
TIMESTAMP WITH LOCAL TZ
VARCHAR2
VARCHAR2
INTERVAL YEAR TO MONTH
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
INTERVAL DAY TO SECOND
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
LONG
X
X
X
X
LONG RAW
VARCHAR2
X
X
VARCHAR2
BLOB
X
X
X
X
CLOB
X
X
X
X
ROWID
VARCHAR2
X
X
ROWID
NCHAR
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
BINARY_FLOAT
BINARY_DOUBLE
NVARCHAR2
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
BINARY_FLOAT
BINARY_DOUBLE
NCLOB
X
X
X
X
TIMESTAMP WITH TZ
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
VARCHAR2
VARCHAR2
TIMESTAMP WITH LTZ
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
VARCHAR2
VARCHAR2
BINARY_FLOAT
VARCHAR2
VARCHAR2
BINARY_FLOAT
BINARY_DOUBLE
BINARY_DOUBLE
VARCHAR2
VARCHAR2
BINARY_DOUBLE
BINARY_DOUBLE
JSON
X
X
X
X
Last updated