Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

CAST Operator

Description

The CAST operator can be used to explicitly cast one data type to another in the SELECT statement. A query list or a value expression in the WHERE clause can be cast to another data type.

Depending on the situation, data type can be automatically converted without suing the CAST operator. For details, see Implicit Type Conversion.

See Casting a String to Date/Time Type regarding to convert the string of date/time type into date/time type.

The following table shows a summary of explicit type conversions (casts) using the CAST operator in CUBRID.

From To

EN

AN

VC

FC

VB

FB

BLOB

CLOB

D

T

UT

DT

S

MS

SQ

EN

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

No

No

No

AN

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

No

No

No

VC

Yes

Yes

Yes*

Yes*

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

FC

Yes

Yes

Yes*

Yes*

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

VB

No

No

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

FB

No

No

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

BLOB

No

No

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

CLOB

No

No

Yes

Yes

Yes

Yes

No

Yes

No

No

No

No

No

No

No

D

No

No

Yes

Yes

No

No

No

No

Yes

No

Yes

Yes

No

No

No

T

No

No

Yes

Yes

No

No

No

No

No

Yes

No

No

No

No

No

UT

No

No

Yes

Yes

No

No

No

No

Yes

Yes

Yes

Yes

No

No

No

DT

No

No

Yes

Yes

No

No

No

No

Yes

Yes

Yes

Yes

No

No

No

S

No

No

No

No

No

No

No

No

No

No

No

No

Yes

Yes

Yes

MS

No

No

No

No

No

No

No

No

No

No

No

No

Yes

Yes

Yes

SQ

No

No

No

No

No

No

No

No

No

No

No

No

Yes

Yes

Yes

* The CAST operation is allowed only when the value expression and the data type to be cast have the same character set.

Data Type Key
  • EN: Exact numeric data type (INTEGER, SMALLINT, BIGINT, NUMERIC, DECIMAL)
  • AN: Approximate numeric data type (FLOAT/REALDOUBLE PRECISION, MONETARY)
  • VC: Variable-length character string (VARCHAR(n), NCHAR VARYING(n))
  • FC: Fixed-length character string (CHAR(n), NCHAR(n))
  • VB: Variable-length bit string (BIT VARYING(n))
  • FB: Fixed-length bit string (BIT(n))
  • BLOB: Binary data that is stored outside DB
  • CLOB: String data that is stored inside DB
  • D: DATE
  • T: TIME
  • DTDATETIME
  • UT: TIMESTAMP
  • S: SET
  • MS: MULTISET
  • SQ: LIST (=SEQUENCE)
Syntax

CAST (cast_operand AS cast_target)

 

cast_operand:

value expression

NULL

 

cast_target:

data type

  • cast_operand: Declares the value to cast to a different data type.
  • cast_target: Specifies the type to cast to.
Example

The following example shows how to explicitly cast and return a VARCHAR record in kg unit to a FLOAT.

--operation after casting character as INT type returns 2

SELECT (1+CAST ('1' AS INT));

  (1+ cast('1' as integer))

===========================

                          2

 

--cannot cast the string which is out of range as SMALLINT

SELECT (1+CAST('1234567890' AS SMALLINT));

 

ERROR: Cannot coerce value of domain "character" to domain "smallint".

--operation after casting returns 1+1234567890

SELECT (1+CAST('1234567890' AS INT));

 (1+ cast('1234567890' as integer))

====================================

                          1234567891

 

--'1234.567890' is casted to 1235 after rounding up

SELECT (1+CAST('1234.567890' AS INT));

 (1+ cast('1234.567890' as integer))

====================================

  1236

 

--'1234.567890' is casted to string containing only first 5 letters.

SELECT (CAST('1234.567890' AS CHAR(5)));

 ( 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: Cannot coerce value of domain "numeric" to domain "character".

--numeric type can be casted to CHAR type only when enough length is specified

SELECT (CAST(1234.567890 AS CHAR(11)));

 ( 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));

 ( 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));

 ( cast('2008-12-25 10:30:20' as timestamp))

=============================================

  10:30:20 AM 12/25/2008

 

SELECT (CAST('10:30:20' AS TIME));

 ( 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));

 ( cast('2008-12-25 10:30:20' as time))

========================================

  10:30:20 AM

 

--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));

 ( cast(timestamp '2008-12-25 10:30:20' as time))

==================================================

  10:30:20 AM

 

SELECT CAST('abcde' AS BLOB);

 cast('abcde' as blob)

======================

file:/home1/user1/db/tdb/lob/ces_743/ces_temp.00001283232024309172_1342

 

SELECT CAST(B'11010000' as varchar(10));

  cast(B'11010000' as varchar(10))

====================================

  'd0'

 

SELECT CAST('1A' AS BLOB);

 cast('1A' as bit(16))

=================================

  X'1a00'

Remark
  • CAST is allowed only between data types having the same character set.
  • If you cast an approximate data type to integer type, the number is rounded to zero decimal places.
  • If you cast a numeric data type to string character type, it should be longer than the length of significant figures + decimal point. An error occurs otherwise.
  • If you cast a character string type A to a character string type B, B should be longer than the A. The end of character string is truncated otherwise.
  • If you cast a character string type A to a date-time date type B, it is converted only when literal of A and B type match one another. An error occurs otherwise.
  • You must explicitly do type casting for numeric data stored in a character string so that an arithmetic operation can be performed.