SQL Reference > Type Functions

Type Functions

This page covers type functions in Rockset. For more on Rockset types, check out the data types page.

To cast values from one type to another, you can use one of four cast functions: CAST, TRY_CAST, STATIC_CAST and TRY_STATIC_CAST.

CAST

CAST(x AS type)

Lexical cast that, in addition to STATIC_CAST, supports casting between string and other scalar types. Errors the query if the cast is not supported.

Supported lexical casts are shown in the table below.

FromToQueryResult
intfloatSELECT CAST(10 as float)10
intboolSELECT CAST(0 as bool)false
intstringSELECT CAST(10 as string)10
boolintSELECT CAST(true AS int)1
boolfloatSELECT CAST(true as float)1
boolstringSELECT CAST(true as string)true
floatintSELECT CAST(2.5 as int)2
floatboolSELECT CAST(2.5 as bool)true
floatstringSELECT CAST(2.5 as string)2.5
stringintSELECT CAST('2' AS int)2
stringfloatSELECT CAST('2.5' as float)2.5
stringboolSELECT CAST('false' as bool)false
stringbytesSELECT CAST('hello' as bytes)'aGVsbG8='
stringdateSELECT CAST('2018-05-26' AS date)2018-05-26
stringtimeSELECT CAST('10:30:20' AS time)10:30:20
stringdatetimeSELECT CAST('2018-05-26 10:30:20.345' as datetime)2018-05-26T10:30:20
stringtimestampSELECT CAST('2018-05-26 10:30:20.345Z' as timestamp)'2018-05-26T10:30:20.345000Z'
bytesstringSELECT CAST(bytes 'hello' as string)'hello'
datestringSELECT CAST(DATE(2018, 5, 26) as string)'2018-05-26'
timestringSELECT CAST(TIME(10, 30, 20) as string)'10:30:20'
datetimestringSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as string)'2018-05-26T10:30:20'
timestampstringSELECT CAST(TIMESTAMP_SECONDS(1527373820) as string)'2018-05-26T22:30:20Z'
datetimedateSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as date)2018-05-26
datetimetimeSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as time)10:30:20
datedatetimeSELECT CAST(DATE(2018, 5, 26) as datetime)2018-05-26T00:00:00

More examples:

SELECT
    CAST(2.0 AS int)
Error

TRY_CAST

TRY_CAST(x AS type)

Same as CAST, except it doesn’t error out the query for an unsupported cast; a null is returned instead.

SELECT
    TRY_CAST('foo' AS int)
null
SELECT
    CAST('2.0' AS int)
null

STATIC_CAST

STATIC_CAST(x AS type)

Static cast that converts values from one data type to another. Supports casts between numeric types and casts to bool with Python semantics. Errors the query if the cast is not supported.

Supported static casts are show below.

FromToQueryResult
intfloatSELECT STATIC_CAST(10 as float)10
intboolSELECT STATIC_CAST(0 as bool)false
boolintSELECT STATIC_CAST(true AS int)1
boolfloatSELECT STATIC_CAST(true AS float)1
floatintSELECT CAST(2.5 as int)2
floatboolSELECT STATIC_CAST(3.5 AS bool)true
stringbytesSELECT STATIC_CAST('hello' as bytes)'aGVsbG8='
bytesboolSELECT STATIC_CAST(bytes'hello' as bool)true
bytesstringSELECT CAST(bytes 'hello' as string)'hello'

More examples:

SELECT
    STATIC_CAST(0.0 AS bool)
false
SELECT
    STATIC_CAST(bytes '' as bool)
false
SELECT
    STATIC_CAST('2' AS int)
Error
SELECT
    STATIC_CAST(null_value AS bool)
Error

TRY_STATIC_CAST

TRY_STATIC_CAST(x AS type)

Same as STATIC_CAST, except it doesn’t error out the query for an unsupported cast; a null is returned instead.

SELECT
    TRY_STATIC_CAST('2' AS int)
null
SELECT
    STATIC_CAST(null_value AS bool)
null

IS_SCALAR

IS_SCALAR(x)

Returns true if x is a scalar, which is any type except array, object, and null.

Following table shows what TYPEOF returns for each of the supported data types.

TypeQueryResult
intSELECT IS_SCALAR(10)'true'
floatSELECT IS_SCALAR(2.0)'true'
boolSELECT IS_SCALAR(true)'true'
stringSELECT IS_SCALAR('hello')'true'
bytesSELECT IS_SCALAR(bytes 'hello')'true'
SQL nullSELECT IS_SCALAR(null)null
JSON nullSELECT IS_SCALAR(null_value)'true'
arraySELECT IS_SCALAR(ARRAY_CREATE(10, 20, 30))'false'
objectSELECT IS_SCALAR(OBJECT(ARRAY_CREATE('field1'), ARRAY_CREATE('hello')))'false'
dateSELECT IS_SCALAR(DATE(2018, 5, 26))'true'
timeSELECT IS_SCALAR(TIME(10, 30, 20))'true'
datetimeSELECT IS_SCALAR(DATETIME(2018, 5, 26, 10, 30, 20))'true'
timestampSELECT IS_SCALAR(TIMESTAMP_SECONDS(1527373820))'true'
month_intervalSELECT IS_SCALAR(INTERVAL 5 MONTH)'true'
microsecond_intervalSELECT IS_SCALAR(INTERVAL 3 HOUR)'true'

TYPEOF

TYPEOF(x)

Returns a string, name of the type of x.

Following table shows what TYPEOF returns for each of the supported data types.

TypeQueryResult
intSELECT TYPEOF(10)'int'
floatSELECT TYPEOF(2.0)'float'
boolSELECT TYPEOF(true)'bool'
stringSELECT TYPEOF('hello')'string'
bytesSELECT TYPEOF(bytes 'hello')'bytes'
SQL nullSELECT TYPEOF(null)'null'
JSON nullSELECT TYPEOF(null_value)'null_type'
arraySELECT TYPEOF(ARRAY_CREATE(10, 20, 30))'array'
objectSELECT TYPEOF(OBJECT(ARRAY_CREATE('field1'), ARRAY_CREATE('hello')))'object'
dateSELECT TYPEOF(DATE(2018, 5, 26))'date'
timeSELECT TYPEOF(TIME(10, 30, 20))'time'
datetimeSELECT TYPEOF(DATETIME(2018, 5, 26, 10, 30, 20))'datetime'
timestampSELECT TYPEOF(TIMESTAMP_SECONDS(1527373820))'timestamp'
month_intervalSELECT TYPEOF(INTERVAL 5 MONTH)'month_interval'
microsecond_intervalSELECT TYPEOF(INTERVAL 3 HOUR)'microsecond_interval'