CAST 연산자

설명

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 

주의 사항