CAST 연산자를 SELECT 문에서 어떤 값의 데이터 타입을 다른 데이터 타입으로 명시적으로 변환하는 데 사용할 수 있다. 조회 리스트 또는 WHERE 절의 값 수식을 다른 데이터 타입으로 변환할 수 있다.
CUBRID에서 CAST 연산자를 사용한 명시적인 타입 변환에 대해서 정리하면 다음의 표와 같다.
|
EN |
AN |
VC |
FC |
VB |
FB |
BLOB |
CLOB |
D |
T |
UT |
DT |
S |
MS |
SQ |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
EN |
O |
O |
O |
O |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
AN |
O |
O |
O |
O |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
VC |
O |
O |
O* |
O* |
O |
O |
O |
O |
O |
O |
O |
O |
X |
X |
X |
FC |
O |
O |
O* |
O* |
O |
O |
O |
O |
O |
O |
O |
O |
X |
X |
X |
VB |
X |
X |
O |
O |
O |
O |
O |
O |
X |
X |
X |
X |
X |
X |
X |
FB |
X |
X |
O |
O |
O |
O |
O |
O |
X |
X |
X |
X |
X |
X |
X |
BLOB |
X |
X |
O |
O |
O |
O |
O |
X |
X |
X |
X |
X |
X |
X |
X |
CLOB |
X |
X |
O |
O |
O |
O |
X |
O |
X |
X |
X |
X |
X |
X |
X |
D |
X |
X |
O |
O |
X |
X |
X |
X |
O |
X |
O |
O |
X |
X |
X |
T |
X |
X |
O |
O |
X |
X |
X |
X |
X |
O |
X |
X |
X |
X |
X |
UT |
X |
X |
O |
O |
X |
X |
X |
X |
O |
O |
O |
O |
X |
X |
X |
DT |
X |
X |
O |
O |
X |
X |
X |
X |
O |
O |
O |
O |
X |
X |
X |
S |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
O |
O |
O |
MS |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
O |
O |
O |
SQ |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
O |
O |
O |
* 이 경우에 CAST 연산은 값 수식과 변환할 데이터 타입이 같은 문자 세트를 가질 경우에만 허용된다.
CAST (cast_operand AS cast_target)
cast_operand :
• value expression
• NULL
cast_target :
• data type
--operation after casting character as INT type returns 2
SELECT (1+CAST ('1' AS INT));
===========================
2
--cannot cast the string which is out of range as SMALLINT
SELECT (1+CAST('1234567890' AS SMALLINT));
ERROR
--operation after casting returns 1+1234567890
SELECT (1+CAST('1234567890' AS INT));
====================================
1234567891
--'1234.567890' is casted to 1235 after rounding up
SELECT (1+CAST('1234.567890' AS INT));
======================
1236
--'1234.567890' is casted to string containing only first 5 letters.
SELECT (CAST('1234.567890' AS CHAR(5)));
======================
'1234.'
--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(5)));
ERROR
--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(11)));
======================
'1234.567890'
--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS VARCHAR));
======================
'1234.567890'
--string can be casted to time/date types only when its literal is correctly specified
SELECT (CAST('2008-12-25 10:30:20' AS TIMESTAMP));
=============================================
10:30:20 AM 12/25/2008
SELECT (CAST('10:30:20' AS TIME));
==================================================
10:30:20 AM
--string can be casted to TIME type when its literal is same as TIME’s.
SELECT (CAST('2008-12-25 10:30:20' AS TIME));
ERROR
--string can be casted to TIME type after specifying its type of the string
SELECT (CAST(TIMESTAMP'2008-12-25 10:30:20' AS TIME));
==================================================
10:30:20 AM
SELECT CAST('abcde' AS BLOB);
======================
file:/home1/user1/db/tdb/lob/ces_743/ces_temp.00001283232024309172_1342