Type Conversion Functions
Common Issues with Data Conversion
ClickHouse generally uses the same behavior as C++ programs.
to<type>
functions and cast behave differently in some cases, for example in case of LowCardinality: cast removes LowCardinality trait to<type>
functions don't. The same with Nullable, this behaviour is not compatible with SQL standard, and it can be changed using cast_keep_nullable setting.
Be aware of potential data loss if values of a datatype are converted to a smaller datatype (for example from Int64
to Int32
) or between
incompatible datatypes (for example from String
to Int
). Make sure to check carefully if the result is as expected.
Example:
SELECT
toTypeName(toLowCardinality('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
┌─source_type────────────┬─to_type_result_type────┬─cast_result_type─┐
│ LowCardinality(String) │ LowCardinality(String) │ String │
└────────────────────────┴────────────────────────┴──────────────────┘
SELECT
toTypeName(toNullable('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String) │ String │
└──────────────────┴─────────────────────┴──────────────────┘
SELECT
toTypeName(toNullable('') AS val) AS source_type,
toTypeName(toString(val)) AS to_type_result_type,
toTypeName(CAST(val, 'String')) AS cast_result_type
SETTINGS cast_keep_nullable = 1
┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String) │ Nullable(String) │
└──────────────────┴─────────────────────┴──────────────────┘
toBool
Converts an input value to a value of type Bool
. Throws an exception in case of an error.
Syntax
toBool(expr)
Arguments
expr
— Expression returning a number or a string. Expression.
Supported arguments:
- Values of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- Strings
true
orfalse
(case-insensitive).
Returned value
- Returns
true
orfalse
based on evaluation of the argument. Bool.
Example
Query:
SELECT
toBool(toUInt8(1)),
toBool(toInt8(-1)),
toBool(toFloat32(1.01)),
toBool('true'),
toBool('false'),
toBool('FALSE')
FORMAT Vertical
Result:
toBool(toUInt8(1)): true
toBool(toInt8(-1)): true
toBool(toFloat32(1.01)): true
toBool('true'): true
toBool('false'): false
toBool('FALSE'): false
toInt8
Converts an input value to a value of type Int8
. Throws an exception in case of an error.
Syntax
toInt8(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8('0xc0fe');
.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs.
This is not considered an error.
For example: SELECT toInt8(128) == -128;
.
Returned value
- 8-bit integer value. Int8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt8(-8),
toInt8(-8.8),
toInt8('-8')
FORMAT Vertical;
Result:
Row 1:
──────
toInt8(-8): -8
toInt8(-8.8): -8
toInt8('-8'): -8
See also
toInt8OrZero
Like toInt8
, this function converts an input value to a value of type Int8 but returns 0
in case of an error.
Syntax
toInt8OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of ordinary Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 8-bit integer value if successful, otherwise
0
. Int8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt8OrZero('-8'),
toInt8OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt8OrZero('-8'): -8
toInt8OrZero('abc'): 0
See also
toInt8OrNull
Like toInt8
, this function converts an input value to a value of type Int8 but returns NULL
in case of an error.
Syntax
toInt8OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt8OrNull('-8'),
toInt8OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt8OrNull('-8'): -8
toInt8OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt8OrDefault
Like toInt8
, this function converts an input value to a value of type Int8 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt8OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt8
is unsuccessful. Int8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8OrDefault('0xc0fe', CAST('-1', 'Int8'));
.
If the input value cannot be represented within the bounds of Int8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 8-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int8.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt8OrDefault('-8', CAST('-1', 'Int8')),
toInt8OrDefault('abc', CAST('-1', 'Int8'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt8OrDefault('-8', CAST('-1', 'Int8')): -8
toInt8OrDefault('abc', CAST('-1', 'Int8')): -1
See also
toInt16
Converts an input value to a value of type Int16
. Throws an exception in case of an error.
Syntax
toInt16(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16('0xc0fe');
.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs.
This is not considered an error.
For example: SELECT toInt16(32768) == -32768;
.
Returned value
- 16-bit integer value. Int16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt16(-16),
toInt16(-16.16),
toInt16('-16')
FORMAT Vertical;
Result:
Row 1:
──────
toInt16(-16): -16
toInt16(-16.16): -16
toInt16('-16'): -16
See also
toInt16OrZero
Like toInt16
, this function converts an input value to a value of type Int16 but returns 0
in case of an error.
Syntax
toInt16OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered as an error.
Returned value
- 16-bit integer value if successful, otherwise
0
. Int16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt16OrZero('-16'),
toInt16OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt16OrZero('-16'): -16
toInt16OrZero('abc'): 0
See also
toInt16OrNull
Like toInt16
, this function converts an input value to a value of type Int16 but returns NULL
in case of an error.
Syntax
toInt16OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt16OrNull('-16'),
toInt16OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt16OrNull('-16'): -16
toInt16OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt16OrDefault
Like toInt16
, this function converts an input value to a value of type Int16 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt16OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt16
is unsuccessful. Int16.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16OrDefault('0xc0fe', CAST('-1', 'Int16'));
.
If the input value cannot be represented within the bounds of Int16, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 16-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int16.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt16OrDefault('-16', CAST('-1', 'Int16')),
toInt16OrDefault('abc', CAST('-1', 'Int16'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt16OrDefault('-16', CAST('-1', 'Int16')): -16
toInt16OrDefault('abc', CAST('-1', 'Int16')): -1
See also
toInt32
Converts an input value to a value of type Int32
. Throws an exception in case of an error.
Syntax
toInt32(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32('0xc0fe');
.
If the input value cannot be represented within the bounds of Int32, the result over or under flows.
This is not considered an error.
For example: SELECT toInt32(2147483648) == -2147483648;
Returned value
- 32-bit integer value. Int32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt32(-32),
toInt32(-32.32),
toInt32('-32')
FORMAT Vertical;
Result:
Row 1:
──────
toInt32(-32): -32
toInt32(-32.32): -32
toInt32('-32'): -32
See also
toInt32OrZero
Like toInt32
, this function converts an input value to a value of type Int32 but returns 0
in case of an error.
Syntax
toInt32OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 32-bit integer value if successful, otherwise
0
. Int32
The function uses rounding towards zero, meaning it truncate fractional digits of numbers.
Example
Query:
SELECT
toInt32OrZero('-32'),
toInt32OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt32OrZero('-32'): -32
toInt32OrZero('abc'): 0
See also
toInt32OrNull
Like toInt32
, this function converts an input value to a value of type Int32 but returns NULL
in case of an error.
Syntax
toInt32OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt32OrNull('-32'),
toInt32OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt32OrNull('-32'): -32
toInt32OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt32OrDefault
Like toInt32
, this function converts an input value to a value of type Int32 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt32OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt32
is unsuccessful. Int32.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt32OrDefault('0xc0fe', CAST('-1', 'Int32'));
.
If the input value cannot be represented within the bounds of Int32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 32-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int32.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt32OrDefault('-32', CAST('-1', 'Int32')),
toInt32OrDefault('abc', CAST('-1', 'Int32'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt32OrDefault('-32', CAST('-1', 'Int32')): -32
toInt32OrDefault('abc', CAST('-1', 'Int32')): -1
See also
toInt64
Converts an input value to a value of type Int64
. Throws an exception in case of an error.
Syntax
toInt64(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported types:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64('0xc0fe');
.
If the input value cannot be represented within the bounds of Int64, the result over or under flows.
This is not considered an error.
For example: SELECT toInt64(9223372036854775808) == -9223372036854775808;
Returned value
- 64-bit integer value. Int64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt64(-64),
toInt64(-64.64),
toInt64('-64')
FORMAT Vertical;
Result:
Row 1:
──────
toInt64(-64): -64
toInt64(-64.64): -64
toInt64('-64'): -64
See also
toInt64OrZero
Like toInt64
, this function converts an input value to a value of type Int64 but returns 0
in case of an error.
Syntax
toInt64OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 64-bit integer value if successful, otherwise
0
. Int64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt64OrZero('-64'),
toInt64OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt64OrZero('-64'): -64
toInt64OrZero('abc'): 0
See also
toInt64OrNull
Like toInt64
, this function converts an input value to a value of type Int64 but returns NULL
in case of an error.
Syntax
toInt64OrNull(x)
Arguments
x
— A String representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt64OrNull('-64'),
toInt64OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt64OrNull('-64'): -64
toInt64OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt64OrDefault
Like toInt64
, this function converts an input value to a value of type Int64 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt64OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt64
is unsuccessful. Int64.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt64OrDefault('0xc0fe', CAST('-1', 'Int64'));
.
If the input value cannot be represented within the bounds of Int64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 64-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int64.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt64OrDefault('-64', CAST('-1', 'Int64')),
toInt64OrDefault('abc', CAST('-1', 'Int64'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt64OrDefault('-64', CAST('-1', 'Int64')): -64
toInt64OrDefault('abc', CAST('-1', 'Int64')): -1
See also
toInt128
Converts an input value to a value of type Int128
. Throws an exception in case of an error.
Syntax
toInt128(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128('0xc0fe');
.
If the input value cannot be represented within the bounds of Int128, the result over or under flows. This is not considered an error.
Returned value
- 128-bit integer value. Int128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt128(-128),
toInt128(-128.8),
toInt128('-128')
FORMAT Vertical;
Result:
Row 1:
──────
toInt128(-128): -128
toInt128(-128.8): -128
toInt128('-128'): -128
See also
toInt128OrZero
Like toInt128
, this function converts an input value to a value of type Int128 but returns 0
in case of an error.
Syntax
toInt128OrZero(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 128-bit integer value if successful, otherwise
0
. Int128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt128OrZero('-128'),
toInt128OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt128OrZero('-128'): -128
toInt128OrZero('abc'): 0
See also
toInt128OrNull
Like toInt128
, this function converts an input value to a value of type Int128 but returns NULL
in case of an error.
Syntax
toInt128OrNull(x)
Arguments
x
— A String representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt128OrNull('-128'),
toInt128OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt128OrNull('-128'): -128
toInt128OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt128OrDefault
Like toInt128
, this function converts an input value to a value of type Int128 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt128OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt128
is unsuccessful. Int128.
Supported arguments:
- (U)Int8/16/32/64/128/256.
- Float32/64.
- String representations of (U)Int8/16/32/128/256.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128OrDefault('0xc0fe', CAST('-1', 'Int128'));
.
If the input value cannot be represented within the bounds of Int128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 128-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int128.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt128OrDefault('-128', CAST('-1', 'Int128')),
toInt128OrDefault('abc', CAST('-1', 'Int128'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt128OrDefault('-128', CAST('-1', 'Int128')): -128
toInt128OrDefault('abc', CAST('-1', 'Int128')): -1
See also
toInt256
Converts an input value to a value of type Int256
. Throws an exception in case of an error.
Syntax
toInt256(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256('0xc0fe');
.
If the input value cannot be represented within the bounds of Int256, the result over or under flows. This is not considered an error.
Returned value
- 256-bit integer value. Int256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt256(-256),
toInt256(-256.256),
toInt256('-256')
FORMAT Vertical;
Result:
Row 1:
──────
toInt256(-256): -256
toInt256(-256.256): -256
toInt256('-256'): -256
See also
toInt256OrZero
Like toInt256
, this function converts an input value to a value of type Int256 but returns 0
in case of an error.
Syntax
toInt256OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 256-bit integer value if successful, otherwise
0
. Int256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt256OrZero('-256'),
toInt256OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt256OrZero('-256'): -256
toInt256OrZero('abc'): 0
See also
toInt256OrNull
Like toInt256
, this function converts an input value to a value of type Int256 but returns NULL
in case of an error.
Syntax
toInt256OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toInt256OrNull('-256'),
toInt256OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toInt256OrNull('-256'): -256
toInt256OrNull('abc'): ᴺᵁᴸᴸ
See also
toInt256OrDefault
Like toInt256
, this function converts an input value to a value of type Int256 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toInt256OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeInt256
is unsuccessful. Int256.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
- String representations of binary and hexadecimal values, e.g.
SELECT toInt256OrDefault('0xc0fe', CAST('-1', 'Int256'));
If the input value cannot be represented within the bounds of Int256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 256-bit integer value if successful, otherwise returns the default value if passed or
0
if not. Int256.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toInt256OrDefault('-256', CAST('-1', 'Int256')),
toInt256OrDefault('abc', CAST('-1', 'Int256'))
FORMAT Vertical;
Result:
Row 1:
──────
toInt256OrDefault('-256', CAST('-1', 'Int256')): -256
toInt256OrDefault('abc', CAST('-1', 'Int256')): -1
See also
toUInt8
Converts an input value to a value of type UInt8
. Throws an exception in case of an error.
Syntax
toUInt8(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs.
This is not considered an error.
For example: SELECT toUInt8(256) == 0;
.
Returned value
- 8-bit unsigned integer value. UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt8(8),
toUInt8(8.8),
toUInt8('8')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt8(8): 8
toUInt8(8.8): 8
toUInt8('8'): 8
See also
toUInt8OrZero
Like toUInt8
, this function converts an input value to a value of type UInt8 but returns 0
in case of an error.
Syntax
toUInt8OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of ordinary Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 8-bit unsigned integer value if successful, otherwise
0
. UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt8OrZero('-8'),
toUInt8OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt8OrZero('-8'): 0
toUInt8OrZero('abc'): 0
See also
toUInt8OrNull
Like toUInt8
, this function converts an input value to a value of type UInt8 but returns NULL
in case of an error.
Syntax
toUInt8OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt8OrNull('8'),
toUInt8OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt8OrNull('8'): 8
toUInt8OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt8OrDefault
Like toUInt8
, this function converts an input value to a value of type UInt8 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt8OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeUInt8
is unsuccessful. UInt8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrDefault('0xc0fe', CAST('0', 'UInt8'));
.
If the input value cannot be represented within the bounds of UInt8, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 8-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt8.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt8OrDefault('8', CAST('0', 'UInt8')),
toUInt8OrDefault('abc', CAST('0', 'UInt8'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt8OrDefault('8', CAST('0', 'UInt8')): 8
toUInt8OrDefault('abc', CAST('0', 'UInt8')): 0
See also
toUInt16
Converts an input value to a value of type UInt16
. Throws an exception in case of an error.
Syntax
toUInt16(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs.
This is not considered an error.
For example: SELECT toUInt16(65536) == 0;
.
Returned value
- 16-bit unsigned integer value. UInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt16(16),
toUInt16(16.16),
toUInt16('16')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt16(16): 16
toUInt16(16.16): 16
toUInt16('16'): 16
See also
toUInt16OrZero
Like toUInt16
, this function converts an input value to a value of type UInt16 but returns 0
in case of an error.
Syntax
toUInt16OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered as an error.
Returned value
- 16-bit unsigned integer value if successful, otherwise
0
. UInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt16OrZero('16'),
toUInt16OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt16OrZero('16'): 16
toUInt16OrZero('abc'): 0
See also
toUInt16OrNull
Like toUInt16
, this function converts an input value to a value of type UInt16 but returns NULL
in case of an error.
Syntax
toUInt16OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt16OrNull('16'),
toUInt16OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt16OrNull('16'): 16
toUInt16OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt16OrDefault
Like toUInt16
, this function converts an input value to a value of type UInt16 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt16OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeUInt16
is unsuccessful. UInt16.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16OrDefault('0xc0fe', CAST('0', 'UInt16'));
.
If the input value cannot be represented within the bounds of UInt16, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 16-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt16.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt16OrDefault('16', CAST('0', 'UInt16')),
toUInt16OrDefault('abc', CAST('0', 'UInt16'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt16OrDefault('16', CAST('0', 'UInt16')): 16
toUInt16OrDefault('abc', CAST('0', 'UInt16')): 0
See also
toUInt32
Converts an input value to a value of type UInt32
. Throws an exception in case of an error.
Syntax
toUInt32(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt32, the result over or under flows.
This is not considered an error.
For example: SELECT toUInt32(4294967296) == 0;
Returned value
- 32-bit unsigned integer value. UInt32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt32(32),
toUInt32(32.32),
toUInt32('32')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt32(32): 32
toUInt32(32.32): 32
toUInt32('32'): 32
See also
toUInt32OrZero
Like toUInt32
, this function converts an input value to a value of type UInt32 but returns 0
in case of an error.
Syntax
toUInt32OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 32-bit unsigned integer value if successful, otherwise
0
. UInt32
The function uses rounding towards zero , meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt32OrZero('32'),
toUInt32OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt32OrZero('32'): 32
toUInt32OrZero('abc'): 0
See also
toUInt32OrNull
Like toUInt32
, this function converts an input value to a value of type UInt32 but returns NULL
in case of an error.
Syntax
toUInt32OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero , meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt32OrNull('32'),
toUInt32OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt32OrNull('32'): 32
toUInt32OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt32OrDefault
Like toUInt32
, this function converts an input value to a value of type UInt32 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt32OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeUInt32
is unsuccessful. UInt32.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt32OrDefault('0xc0fe', CAST('0', 'UInt32'));
.
If the input value cannot be represented within the bounds of UInt32, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 32-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt32.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt32OrDefault('32', CAST('0', 'UInt32')),
toUInt32OrDefault('abc', CAST('0', 'UInt32'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt32OrDefault('32', CAST('0', 'UInt32')): 32
toUInt32OrDefault('abc', CAST('0', 'UInt32')): 0
See also
toUInt64
Converts an input value to a value of type UInt64
. Throws an exception in case of an error.
Syntax
toUInt64(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported types:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt64, the result over or under flows.
This is not considered an error.
For example: SELECT toUInt64(18446744073709551616) == 0;
Returned value
- 64-bit unsigned integer value. UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt64(64),
toUInt64(64.64),
toUInt64('64')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt64(64): 64
toUInt64(64.64): 64
toUInt64('64'): 64
See also
toUInt64OrZero
Like toUInt64
, this function converts an input value to a value of type UInt64 but returns 0
in case of an error.
Syntax
toUInt64OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 64-bit unsigned integer value if successful, otherwise
0
. UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt64OrZero('64'),
toUInt64OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt64OrZero('64'): 64
toUInt64OrZero('abc'): 0
See also
toUInt64OrNull
Like toUInt64
, this function converts an input value to a value of type UInt64 but returns NULL
in case of an error.
Syntax
toUInt64OrNull(x)
Arguments
x
— A String representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt64OrNull('64'),
toUInt64OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt64OrNull('64'): 64
toUInt64OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt64OrDefault
Like toUInt64
, this function converts an input value to a value of type UInt64 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt64OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.defauult
(optional) — The default value to return if parsing to typeUInt64
is unsuccessful. UInt64.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrDefault('0xc0fe', CAST('0', 'UInt64'));
.
If the input value cannot be represented within the bounds of UInt64, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 64-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt64.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt64OrDefault('64', CAST('0', 'UInt64')),
toUInt64OrDefault('abc', CAST('0', 'UInt64'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt64OrDefault('64', CAST('0', 'UInt64')): 64
toUInt64OrDefault('abc', CAST('0', 'UInt64')): 0
See also
toUInt128
Converts an input value to a value of type UInt128
. Throws an exception in case of an error.
Syntax
toUInt128(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt128, the result over or under flows. This is not considered an error.
Returned value
- 128-bit unsigned integer value. UInt128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt128(128),
toUInt128(128.8),
toUInt128('128')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt128(128): 128
toUInt128(128.8): 128
toUInt128('128'): 128
See also
toUInt128OrZero
Like toUInt128
, this function converts an input value to a value of type UInt128 but returns 0
in case of an error.
Syntax
toUInt128OrZero(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 128-bit unsigned integer value if successful, otherwise
0
. UInt128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt128OrZero('128'),
toUInt128OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt128OrZero('128'): 128
toUInt128OrZero('abc'): 0
See also
toUInt128OrNull
Like toUInt128
, this function converts an input value to a value of type UInt128 but returns NULL
in case of an error.
Syntax
toUInt128OrNull(x)
Arguments
x
— A String representation of a number. Expression / String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt128OrNull('128'),
toUInt128OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt128OrNull('128'): 128
toUInt128OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt128OrDefault
Like toUInt128
, this function converts an input value to a value of type UInt128 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt128OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeUInt128
is unsuccessful. UInt128.
Supported arguments:
- (U)Int8/16/32/64/128/256.
- Float32/64.
- String representations of (U)Int8/16/32/128/256.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128OrDefault('0xc0fe', CAST('0', 'UInt128'));
.
If the input value cannot be represented within the bounds of UInt128, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 128-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt128.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt128OrDefault('128', CAST('0', 'UInt128')),
toUInt128OrDefault('abc', CAST('0', 'UInt128'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt128OrDefault('128', CAST('0', 'UInt128')): 128
toUInt128OrDefault('abc', CAST('0', 'UInt128')): 0
See also
toUInt256
Converts an input value to a value of type UInt256
. Throws an exception in case of an error.
Syntax
toUInt256(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt256, the result over or under flows. This is not considered an error.
Returned value
- 256-bit unsigned integer value. Int256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt256(256),
toUInt256(256.256),
toUInt256('256')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt256(256): 256
toUInt256(256.256): 256
toUInt256('256'): 256
See also
toUInt256OrZero
Like toUInt256
, this function converts an input value to a value of type UInt256 but returns 0
in case of an error.
Syntax
toUInt256OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return 0
):
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrZero('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 256-bit unsigned integer value if successful, otherwise
0
. UInt256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt256OrZero('256'),
toUInt256OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt256OrZero('256'): 256
toUInt256OrZero('abc'): 0
See also
toUInt256OrNull
Like toUInt256
, this function converts an input value to a value of type UInt256 but returns NULL
in case of an error.
Syntax
toUInt256OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256.
Unsupported arguments (return \N
)
- String representations of Float32/64 values, including
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrNull('0xc0fe');
.
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Example
Query:
SELECT
toUInt256OrNull('256'),
toUInt256OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toUInt256OrNull('256'): 256
toUInt256OrNull('abc'): ᴺᵁᴸᴸ
See also
toUInt256OrDefault
Like toUInt256
, this function converts an input value to a value of type UInt256 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toUInt256OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeUInt256
is unsuccessful. UInt256.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
Arguments for which the default value is returned:
- String representations of Float32/64 values, including
NaN
andInf
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrDefault('0xc0fe', CAST('0', 'UInt256'));
If the input value cannot be represented within the bounds of UInt256, overflow or underflow of the result occurs. This is not considered an error.
Returned value
- 256-bit unsigned integer value if successful, otherwise returns the default value if passed or
0
if not. UInt256.
- The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- The default value type should be the same as the cast type.
Example
Query:
SELECT
toUInt256OrDefault('-256', CAST('0', 'UInt256')),
toUInt256OrDefault('abc', CAST('0', 'UInt256'))
FORMAT Vertical;
Result:
Row 1:
──────
toUInt256OrDefault('-256', CAST('0', 'UInt256')): 0
toUInt256OrDefault('abc', CAST('0', 'UInt256')): 0
See also
toFloat32
Converts an input value to a value of type Float32
. Throws an exception in case of an error.
Syntax
toFloat32(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values of type (U)Int8/16/32/64/128/256.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float32/64, including
NaN
andInf
. - String representations of Float32/64, including
NaN
andInf
(case-insensitive).
Unsupported arguments:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32('0xc0fe');
.
Returned value
- 32-bit floating point value. Float32.
Example
Query:
SELECT
toFloat32(42.7),
toFloat32('42.7'),
toFloat32('NaN')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat32(42.7): 42.7
toFloat32('42.7'): 42.7
toFloat32('NaN'): nan
See also
toFloat32OrZero
Like toFloat32
, this function converts an input value to a value of type Float32 but returns 0
in case of an error.
Syntax
toFloat32OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256, Float32/64.
Unsupported arguments (return 0
):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32OrZero('0xc0fe');
.
Returned value
- 32-bit Float value if successful, otherwise
0
. Float32.
Example
Query:
SELECT
toFloat32OrZero('42.7'),
toFloat32OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat32OrZero('42.7'): 42.7
toFloat32OrZero('abc'): 0
See also
toFloat32OrNull
Like toFloat32
, this function converts an input value to a value of type Float32 but returns NULL
in case of an error.
Syntax
toFloat32OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256, Float32/64.
Unsupported arguments (return \N
):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32OrNull('0xc0fe');
.
Returned value
- 32-bit Float value if successful, otherwise
\N
. Float32.
Example
Query:
SELECT
toFloat32OrNull('42.7'),
toFloat32OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat32OrNull('42.7'): 42.7
toFloat32OrNull('abc'): ᴺᵁᴸᴸ
See also
toFloat32OrDefault
Like toFloat32
, this function converts an input value to a value of type Float32 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toFloat32OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeFloat32
is unsuccessful. Float32.
Supported arguments:
- Values of type (U)Int8/16/32/64/128/256.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float32/64, including
NaN
andInf
. - String representations of Float32/64, including
NaN
andInf
(case-insensitive).
Arguments for which the default value is returned:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32OrDefault('0xc0fe', CAST('0', 'Float32'));
.
Returned value
- 32-bit Float value if successful, otherwise returns the default value if passed or
0
if not. Float32.
Example
Query:
SELECT
toFloat32OrDefault('8', CAST('0', 'Float32')),
toFloat32OrDefault('abc', CAST('0', 'Float32'))
FORMAT Vertical;
Result:
Row 1:
──────
toFloat32OrDefault('8', CAST('0', 'Float32')): 8
toFloat32OrDefault('abc', CAST('0', 'Float32')): 0
See also
toFloat64
Converts an input value to a value of type Float64
. Throws an exception in case of an error.
Syntax
toFloat64(expr)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.
Supported arguments:
- Values of type (U)Int8/16/32/64/128/256.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float32/64, including
NaN
andInf
. - String representations of type Float32/64, including
NaN
andInf
(case-insensitive).
Unsupported arguments:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64('0xc0fe');
.
Returned value
- 64-bit floating point value. Float64.
Example
Query:
SELECT
toFloat64(42.7),
toFloat64('42.7'),
toFloat64('NaN')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat64(42.7): 42.7
toFloat64('42.7'): 42.7
toFloat64('NaN'): nan
See also
toFloat64OrZero
Like toFloat64
, this function converts an input value to a value of type Float64 but returns 0
in case of an error.
Syntax
toFloat64OrZero(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256, Float32/64.
Unsupported arguments (return 0
):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64OrZero('0xc0fe');
.
Returned value
- 64-bit Float value if successful, otherwise
0
. Float64.
Example
Query:
SELECT
toFloat64OrZero('42.7'),
toFloat64OrZero('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat64OrZero('42.7'): 42.7
toFloat64OrZero('abc'): 0
See also
toFloat64OrNull
Like toFloat64
, this function converts an input value to a value of type Float64 but returns NULL
in case of an error.
Syntax
toFloat64OrNull(x)
Arguments
x
— A String representation of a number. String.
Supported arguments:
- String representations of (U)Int8/16/32/128/256, Float32/64.
Unsupported arguments (return \N
):
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64OrNull('0xc0fe');
.
Returned value
- 64-bit Float value if successful, otherwise
\N
. Float64.
Example
Query:
SELECT
toFloat64OrNull('42.7'),
toFloat64OrNull('abc')
FORMAT Vertical;
Result:
Row 1:
──────
toFloat64OrNull('42.7'): 42.7
toFloat64OrNull('abc'): ᴺᵁᴸᴸ
See also
toFloat64OrDefault
Like toFloat64
, this function converts an input value to a value of type Float64 but returns the default value in case of an error.
If no default
value is passed then 0
is returned in case of an error.
Syntax
toFloat64OrDefault(expr[, default])
Arguments
expr
— Expression returning a number or a string representation of a number. Expression / String.default
(optional) — The default value to return if parsing to typeFloat64
is unsuccessful. Float64.
Supported arguments:
- Values of type (U)Int8/16/32/64/128/256.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float32/64, including
NaN
andInf
. - String representations of Float32/64, including
NaN
andInf
(case-insensitive).
Arguments for which the default value is returned:
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64OrDefault('0xc0fe', CAST('0', 'Float64'));
.
Returned value
- 64-bit Float value if successful, otherwise returns the default value if passed or
0
if not. Float64.
Example
Query:
SELECT
toFloat64OrDefault('8', CAST('0', 'Float64')),
toFloat64OrDefault('abc', CAST('0', 'Float64'))
FORMAT Vertical;
Result:
Row 1:
──────
toFloat64OrDefault('8', CAST('0', 'Float64')): 8
toFloat64OrDefault('abc', CAST('0', 'Float64')): 0
See also
toDate
Converts the argument to Date data type.
If the argument is DateTime or DateTime64, it truncates it and leaves the date component of the DateTime:
SELECT
now() AS x,
toDate(x)
┌───────────────────x─┬─toDate(now())─┐
│ 2022-12-30 13:44:17 │ 2022-12-30 │
└─────────────────────┴───────────────┘
If the argument is a String, it is parsed as Date or DateTime. If it was parsed as DateTime, the date component is being used:
SELECT
toDate('2022-12-30') AS x,
toTypeName(x)
┌──────────x─┬─toTypeName(toDate('2022-12-30'))─┐
│ 2022-12-30 │ Date │
└────────────┴──────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
SELECT
toDate('2022-12-30 01:02:03') AS x,
toTypeName(x)
┌──────────x─┬─toTypeName(toDate('2022-12-30 01:02:03'))─┐
│ 2022-12-30 │ Date │
└────────────┴───────────────────────────────────────────┘
If the argument is a number and looks like a UNIX timestamp (is greater than 65535), it is interpreted as a DateTime, then truncated to Date in the current timezone. The timezone argument can be specified as a second argument of the function. The truncation to Date depends on the timezone:
SELECT
now() AS current_time,
toUnixTimestamp(current_time) AS ts,
toDateTime(ts) AS time_Amsterdam,
toDateTime(ts, 'Pacific/Apia') AS time_Samoa,
toDate(time_Amsterdam) AS date_Amsterdam,
toDate(time_Samoa) AS date_Samoa,
toDate(ts) AS date_Amsterdam_2,
toDate(ts, 'Pacific/Apia') AS date_Samoa_2
Row 1:
──────
current_time: 2022-12-30 13:51:54
ts: 1672404714
time_Amsterdam: 2022-12-30 13:51:54
time_Samoa: 2022-12-31 01:51:54
date_Amsterdam: 2022-12-30
date_Samoa: 2022-12-31
date_Amsterdam_2: 2022-12-30
date_Samoa_2: 2022-12-31
The example above demonstrates how the same UNIX timestamp can be interpreted as different dates in different time zones.
If the argument is a number and it is smaller than 65536, it is interpreted as the number of days since 1970-01-01 (the first UNIX day) and converted to Date. It corresponds to the internal numeric representation of the Date
data type. Example:
SELECT toDate(12345)
┌─toDate(12345)─┐
│ 2003-10-20 │
└───────────────┘
This conversion does not depend on timezones.
If the argument does not fit in the range of the Date type, it results in an implementation-defined behavior, that can saturate to the maximum supported date or overflow:
SELECT toDate(10000000000.)
┌─toDate(10000000000.)─┐
│ 2106-02-07 │
└──────────────────────┘
The function toDate
can be also written in alternative forms:
SELECT
now() AS time,
toDate(time),
DATE(time),
CAST(time, 'Date')
┌────────────────time─┬─toDate(now())─┬─DATE(now())─┬─CAST(now(), 'Date')─┐
│ 2022-12-30 13:54:58 │ 2022-12-30 │ 2022-12-30 │ 2022-12-30 │
└─────────────────────┴───────────────┴─────────────┴─────────────────────┘
toDateOrZero
The same as toDate but returns lower boundary of Date if an invalid argument is received. Only String argument is supported.
Example
Query:
SELECT toDateOrZero('2022-12-30'), toDateOrZero('');
Result:
┌─toDateOrZero('2022-12-30')─┬─toDateOrZero('')─┐
│ 2022-12-30 │ 1970-01-01 │
└────────────────────────────┴──────────────────┘
toDateOrNull
The same as toDate but returns NULL
if an invalid argument is received. Only String argument is supported.
Example
Query:
SELECT toDateOrNull('2022-12-30'), toDateOrNull('');
Result:
┌─toDateOrNull('2022-12-30')─┬─toDateOrNull('')─┐
│ 2022-12-30 │ ᴺᵁᴸᴸ │
└────────────────────────────┴──────────────────┘
toDateOrDefault
Like toDate but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of Date.
Syntax
toDateOrDefault(expr [, default_value])
Example
Query:
SELECT toDateOrDefault('2022-12-30'), toDateOrDefault('', '2023-01-01'::Date);
Result:
┌─toDateOrDefault('2022-12-30')─┬─toDateOrDefault('', CAST('2023-01-01', 'Date'))─┐
│ 2022-12-30 │ 2023-01-01 │
└───────────────────────────────┴─────────────────────────────────────────────────┘
toDateTime
Converts an input value to DateTime.
Syntax
toDateTime(expr[, time_zone ])
Arguments
If expr
is a number, it is interpreted as the number of seconds since the beginning of the Unix Epoch (as Unix timestamp).
If expr
is a String, it may be interpreted as a Unix timestamp or as a string representation of date / date with time.
Thus, parsing of short numbers' string representations (up to 4 digits) is explicitly disabled due to ambiguity, e.g. a string '1999'
may be both a year (an incomplete string representation of Date / DateTime) or a unix timestamp. Longer numeric strings are allowed.
Returned value
- A date time. DateTime
Example
Query:
SELECT toDateTime('2022-12-30 13:44:17'), toDateTime(1685457500, 'UTC');
Result:
┌─toDateTime('2022-12-30 13:44:17')─┬─toDateTime(1685457500, 'UTC')─┐
│ 2022-12-30 13:44:17 │ 2023-05-30 14:38:20 │
└───────────────────────────────────┴───────────────────────────────┘
toDateTimeOrZero
The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received. Only String argument is supported.
Example
Query:
SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('');
Result:
┌─toDateTimeOrZero('2022-12-30 13:44:17')─┬─toDateTimeOrZero('')─┐
│ 2022-12-30 13:44:17 │ 1970-01-01 00:00:00 │
└─────────────────────────────────────────┴──────────────────────┘
toDateTimeOrNull
The same as toDateTime but returns NULL
if an invalid argument is received. Only String argument is supported.
Example
Query:
SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('');
Result:
┌─toDateTimeOrNull('2022-12-30 13:44:17')─┬─toDateTimeOrNull('')─┐
│ 2022-12-30 13:44:17 │ ᴺᵁᴸᴸ │
└─────────────────────────────────────────┴──────────────────────┘
toDateTimeOrDefault
Like toDateTime but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of DateTime.
Syntax
toDateTimeOrDefault(expr [, time_zone [, default_value]])
Example
Query:
SELECT toDateTimeOrDefault('2022-12-30 13:44:17'), toDateTimeOrDefault('', 'UTC', '2023-01-01'::DateTime('UTC'));
Result:
┌─toDateTimeOrDefault('2022-12-30 13:44:17')─┬─toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))─┐
│ 2022-12-30 13:44:17 │ 2023-01-01 00:00:00 │
└────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
toDate32
Converts the argument to the Date32 data type. If the value is outside the range, toDate32
returns the border values supported by Date32. If the argument has Date type, it's borders are taken into account.
Syntax
toDate32(expr)
Arguments
Returned value
- A calendar date. Type Date32.
Example
- The value is within the range:
SELECT toDate32('1955-01-01') AS value, toTypeName(value);
┌──────value─┬─toTypeName(toDate32('1925-01-01'))─┐
│ 1955-01-01 │ Date32 │
└────────────┴────────────────────────────────────┘
- The value is outside the range:
SELECT toDate32('1899-01-01') AS value, toTypeName(value);
┌──────value─┬─toTypeName(toDate32('1899-01-01'))─┐
│ 1900-01-01 │ Date32 │
└────────────┴────────────────────────────────────┘
- With Date argument:
SELECT toDate32(toDate('1899-01-01')) AS value, toTypeName(value);
┌──────value─┬─toTypeName(toDate32(toDate('1899-01-01')))─┐
│ 1970-01-01 │ Date32 │
└────────────┴────────────────────────────────────────────┘
toDate32OrZero
The same as toDate32 but returns the min value of Date32 if an invalid argument is received.
Example
Query:
SELECT toDate32OrZero('1899-01-01'), toDate32OrZero('');
Result:
┌─toDate32OrZero('1899-01-01')─┬─toDate32OrZero('')─┐
│ 1900-01-01 │ 1900-01-01 │
└──────────────────────────────┴────────────────────┘
toDate32OrNull
The same as toDate32 but returns NULL
if an invalid argument is received.
Example
Query:
SELECT toDate32OrNull('1955-01-01'), toDate32OrNull('');
Result:
┌─toDate32OrNull('1955-01-01')─┬─toDate32OrNull('')─┐
│ 1955-01-01 │ ᴺᵁᴸᴸ │
└──────────────────────────────┴────────────────────┘
toDate32OrDefault
Converts the argument to the Date32 data type. If the value is outside the range, toDate32OrDefault
returns the lower border value supported by Date32. If the argument has Date type, it's borders are taken into account. Returns default value if an invalid argument is received.
Example
Query:
SELECT
toDate32OrDefault('1930-01-01', toDate32('2020-01-01')),
toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'));
Result:
┌─toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))─┬─toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))─┐
│ 1930-01-01 │ 2020-01-01 │
└─────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘
toDateTime64
Converts an input value to a value of type DateTime64.
Syntax
toDateTime64(expr, scale, [timezone])
Arguments
expr
— The value. String, UInt32, Float or DateTime.scale
- Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].timezone
(optional) - Time zone of the specified datetime64 object.
Returned value
- A calendar date and time of day, with sub-second precision. DateTime64.
Example
- The value is within the range:
SELECT toDateTime64('1955-01-01 00:00:00.000', 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64('1955-01-01 00:00:00.000', 3))─┐
│ 1955-01-01 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────────────────┘
- As decimal with precision:
SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800., 3))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴──────────────────────────────────────────┘
Without the decimal point the value is still treated as Unix Timestamp in seconds:
SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3) │
└─────────────────────────┴────────────────────────────────────────────┘
- With
timezone
:
SELECT toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value);
┌───────────────────value─┬─toTypeName(toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘
toDateTime64OrZero
Like toDateTime64, this function converts an input value to a value of type DateTime64 but returns the min value of DateTime64 if an invalid argument is received.
Syntax
toDateTime64OrZero(expr, scale, [timezone])
Arguments
expr
— The value. String, UInt32, Float or DateTime.scale
- Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].timezone
(optional) - Time zone of the specified DateTime64 object.
Returned value
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of
DateTime64
:1970-01-01 01:00:00.000
. DateTime64.
Example
Query:
SELECT toDateTime64OrZero('2008-10-12 00:00:00 00:30:30', 3) AS invalid_arg
Result:
┌─────────────invalid_arg─┐
│ 1970-01-01 01:00:00.000 │
└─────────────────────────┘
See also
toDateTime64OrNull
Like toDateTime64, this function converts an input value to a value of type DateTime64 but returns NULL
if an invalid argument is received.
Syntax
toDateTime64OrNull(expr, scale, [timezone])
Arguments
expr
— The value. String, UInt32, Float or DateTime.scale
- Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].timezone
(optional) - Time zone of the specified DateTime64 object.
Returned value
- A calendar date and time of day, with sub-second precision, otherwise
NULL
. DateTime64/NULL.
Example
Query:
SELECT
toDateTime64OrNull('1976-10-18 00:00:00.30', 3) AS valid_arg,
toDateTime64OrNull('1976-10-18 00:00:00 30', 3) AS invalid_arg
Result:
┌───────────────valid_arg─┬─invalid_arg─┐
│ 1976-10-18 00:00:00.300 │ ᴺᵁᴸᴸ │
└─────────────────────────┴─────────────┘
See also
toDateTime64OrDefault
Like toDateTime64, this function converts an input value to a value of type DateTime64, but returns either the default value of DateTime64 or the provided default if an invalid argument is received.
Syntax
toDateTime64OrNull(expr, scale, [timezone, default])
Arguments
expr
— The value. String, UInt32, Float or DateTime.scale
- Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ].timezone
(optional) - Time zone of the specified DateTime64 object.default
(optional) - Default value to return if an invalid argument is received. DateTime64.
Returned value
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of
DateTime64
or thedefault
value if provided. DateTime64.
Example
Query:
SELECT
toDateTime64OrDefault('1976-10-18 00:00:00 30', 3) AS invalid_arg,
toDateTime64OrDefault('1976-10-18 00:00:00 30', 3, 'UTC', toDateTime64('2001-01-01 00:00:00.00',3)) AS invalid_arg_with_default
Result:
┌─────────────invalid_arg─┬─invalid_arg_with_default─┐
│ 1970-01-01 01:00:00.000 │ 2000-12-31 23:00:00.000 │
└─────────────────────────┴──────────────────────────┘
See also
toDecimal32
Converts an input value to a value of type Decimal(9, S)
with scale of S
. Throws an exception in case of an error.
Syntax
toDecimal32(expr, S)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.S
— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values or string representations of type Float32/64.
Unsupported arguments:
- Values or string representations of Float32/64 values
NaN
andInf
(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal32
: ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal32(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal32('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(9, S)
. Decimal32(S).
Example
Query:
SELECT
toDecimal32(2, 1) AS a, toTypeName(a) AS type_a,
toDecimal32(4.2, 2) AS b, toTypeName(b) AS type_b,
toDecimal32('4.2', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical;
Result:
Row 1:
──────
a: 2
type_a: Decimal(9, 1)
b: 4.2
type_b: Decimal(9, 2)
c: 4.2
type_c: Decimal(9, 3)
See also
toDecimal32OrZero
Like toDecimal32
, this function converts an input value to a value of type Decimal(9, S) but returns 0
in case of an error.
Syntax
toDecimal32OrZero(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32OrZero('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal32
: ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Decimal(9, S)
if successful, otherwise0
withS
decimal places. Decimal32(S).
Example
Query:
SELECT
toDecimal32OrZero(toString(-1.111), 5) AS a,
toTypeName(a),
toDecimal32OrZero(toString('Inf'), 5) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: -1.111
toTypeName(a): Decimal(9, 5)
b: 0
toTypeName(b): Decimal(9, 5)
See also
toDecimal32OrNull
Like toDecimal32
, this function converts an input value to a value of type Nullable(Decimal(9, S)) but returns 0
in case of an error.
Syntax
toDecimal32OrNull(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32OrNull('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal32
: ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Nullable(Decimal(9, S))
if successful, otherwise valueNULL
of the same type. Decimal32(S).
Examples
Query:
SELECT
toDecimal32OrNull(toString(-1.111), 5) AS a,
toTypeName(a),
toDecimal32OrNull(toString('Inf'), 5) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: -1.111
toTypeName(a): Nullable(Decimal(9, 5))
b: ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(9, 5))
See also
toDecimal32OrDefault
Like toDecimal32
, this function converts an input value to a value of type Decimal(9, S) but returns the default value in case of an error.
Syntax
toDecimal32OrDefault(expr, S[, default])
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.default
(optional) — The default value to return if parsing to typeDecimal32(S)
is unsuccessful. Decimal32(S).
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32OrDefault('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal32
: ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal32OrDefault(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal32OrDefault('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(9, S)
if successful, otherwise returns the default value if passed or0
if not. Decimal32(S).
Examples
Query:
SELECT
toDecimal32OrDefault(toString(0.0001), 5) AS a,
toTypeName(a),
toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)')) AS b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Decimal(9, 5)
b: -1
toTypeName(b): Decimal(9, 0)
See also
toDecimal64
Converts an input value to a value of type Decimal(18, S)
with scale of S
. Throws an exception in case of an error.
Syntax
toDecimal64(expr, S)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.S
— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values or string representations of type Float32/64.
Unsupported arguments:
- Values or string representations of Float32/64 values
NaN
andInf
(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal64
: ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal64(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal64('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(18, S)
. Decimal64(S).
Example
Query:
SELECT
toDecimal64(2, 1) AS a, toTypeName(a) AS type_a,
toDecimal64(4.2, 2) AS b, toTypeName(b) AS type_b,
toDecimal64('4.2', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical;
Result:
Row 1:
──────
a: 2
type_a: Decimal(18, 1)
b: 4.2
type_b: Decimal(18, 2)
c: 4.2
type_c: Decimal(18, 3)
See also
toDecimal64OrZero
Like toDecimal64
, this function converts an input value to a value of type Decimal(18, S) but returns 0
in case of an error.
Syntax
toDecimal64OrZero(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64OrZero('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal64
: ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Decimal(18, S)
if successful, otherwise0
withS
decimal places. Decimal64(S).
Example
Query:
SELECT
toDecimal64OrZero(toString(0.0001), 18) AS a,
toTypeName(a),
toDecimal64OrZero(toString('Inf'), 18) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Decimal(18, 18)
b: 0
toTypeName(b): Decimal(18, 18)
See also
toDecimal64OrNull
Like toDecimal64
, this function converts an input value to a value of type Nullable(Decimal(18, S)) but returns 0
in case of an error.
Syntax
toDecimal64OrNull(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64OrNull('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal64
: ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Nullable(Decimal(18, S))
if successful, otherwise valueNULL
of the same type. Decimal64(S).
Examples
Query:
SELECT
toDecimal64OrNull(toString(0.0001), 18) AS a,
toTypeName(a),
toDecimal64OrNull(toString('Inf'), 18) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Nullable(Decimal(18, 18))
b: ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(18, 18))
See also
toDecimal64OrDefault
Like toDecimal64
, this function converts an input value to a value of type Decimal(18, S) but returns the default value in case of an error.
Syntax
toDecimal64OrDefault(expr, S[, default])
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.default
(optional) — The default value to return if parsing to typeDecimal64(S)
is unsuccessful. Decimal64(S).
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal64OrDefault('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal64
: ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal64OrDefault(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal64OrDefault('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(18, S)
if successful, otherwise returns the default value if passed or0
if not. Decimal64(S).
Examples
Query:
SELECT
toDecimal64OrDefault(toString(0.0001), 18) AS a,
toTypeName(a),
toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)')) AS b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Decimal(18, 18)
b: -1
toTypeName(b): Decimal(18, 0)
See also
toDecimal128
Converts an input value to a value of type Decimal(38, S)
with scale of S
. Throws an exception in case of an error.
Syntax
toDecimal128(expr, S)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.S
— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values or string representations of type Float32/64.
Unsupported arguments:
- Values or string representations of Float32/64 values
NaN
andInf
(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal128
: ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal128(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal128('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(38, S)
. Decimal128(S).
Example
Query:
SELECT
toDecimal128(99, 1) AS a, toTypeName(a) AS type_a,
toDecimal128(99.67, 2) AS b, toTypeName(b) AS type_b,
toDecimal128('99.67', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical;
Result:
Row 1:
──────
a: 99
type_a: Decimal(38, 1)
b: 99.67
type_b: Decimal(38, 2)
c: 99.67
type_c: Decimal(38, 3)
See also
toDecimal128OrZero
Like toDecimal128
, this function converts an input value to a value of type Decimal(38, S) but returns 0
in case of an error.
Syntax
toDecimal128OrZero(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128OrZero('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal128
: ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Decimal(38, S)
if successful, otherwise0
withS
decimal places. Decimal128(S).
Example
Query:
SELECT
toDecimal128OrZero(toString(0.0001), 38) AS a,
toTypeName(a),
toDecimal128OrZero(toString('Inf'), 38) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Decimal(38, 38)
b: 0
toTypeName(b): Decimal(38, 38)
See also
toDecimal128OrNull
Like toDecimal128
, this function converts an input value to a value of type Nullable(Decimal(38, S)) but returns 0
in case of an error.
Syntax
toDecimal128OrNull(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128OrNull('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal128
: ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Nullable(Decimal(38, S))
if successful, otherwise valueNULL
of the same type. Decimal128(S).
Examples
Query:
SELECT
toDecimal128OrNull(toString(1/42), 38) AS a,
toTypeName(a),
toDecimal128OrNull(toString('Inf'), 38) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.023809523809523808
toTypeName(a): Nullable(Decimal(38, 38))
b: ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(38, 38))
See also
toDecimal128OrDefault
Like toDecimal128
, this function converts an input value to a value of type Decimal(38, S) but returns the default value in case of an error.
Syntax
toDecimal128OrDefault(expr, S[, default])
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.default
(optional) — The default value to return if parsing to typeDecimal128(S)
is unsuccessful. Decimal128(S).
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal128OrDefault('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal128
: ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal128OrDefault(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal128OrDefault('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(38, S)
if successful, otherwise returns the default value if passed or0
if not. Decimal128(S).
Examples
Query:
SELECT
toDecimal128OrDefault(toString(1/42), 18) AS a,
toTypeName(a),
toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)')) AS b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.023809523809523808
toTypeName(a): Decimal(38, 18)
b: -1
toTypeName(b): Decimal(38, 0)
See also
toDecimal256
Converts an input value to a value of type Decimal(76, S)
with scale of S
. Throws an exception in case of an error.
Syntax
toDecimal256(expr, S)
Arguments
expr
— Expression returning a number or a string representation of a number. Expression.S
— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values or string representations of type Float32/64.
Unsupported arguments:
- Values or string representations of Float32/64 values
NaN
andInf
(case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal256
: ( -1 * 10^(76 - S), 1 * 10^(76 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal256(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal256('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(76, S)
. Decimal256(S).
Example
Query:
SELECT
toDecimal256(99, 1) AS a, toTypeName(a) AS type_a,
toDecimal256(99.67, 2) AS b, toTypeName(b) AS type_b,
toDecimal256('99.67', 3) AS c, toTypeName(c) AS type_c
FORMAT Vertical;
Result:
Row 1:
──────
a: 99
type_a: Decimal(76, 1)
b: 99.67
type_b: Decimal(76, 2)
c: 99.67
type_c: Decimal(76, 3)
See also
toDecimal256OrZero
Like toDecimal256
, this function converts an input value to a value of type Decimal(76, S) but returns 0
in case of an error.
Syntax
toDecimal256OrZero(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrZero('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal256
: ( -1 * 10^(76 - S), 1 * 10^(76 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Decimal(76, S)
if successful, otherwise0
withS
decimal places. Decimal256(S).
Example
Query:
SELECT
toDecimal256OrZero(toString(0.0001), 76) AS a,
toTypeName(a),
toDecimal256OrZero(toString('Inf'), 76) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.0001
toTypeName(a): Decimal(76, 76)
b: 0
toTypeName(b): Decimal(76, 76)
See also
toDecimal256OrNull
Like toDecimal256
, this function converts an input value to a value of type Nullable(Decimal(76, S)) but returns 0
in case of an error.
Syntax
toDecimal256OrNull(expr, S)
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrNull('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal256
: ( -1 * 10^(76 - S), 1 * 10^(76 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Returned value
- Value of type
Nullable(Decimal(76, S))
if successful, otherwise valueNULL
of the same type. Decimal256(S).
Examples
Query:
SELECT
toDecimal256OrNull(toString(1/42), 76) AS a,
toTypeName(a),
toDecimal256OrNull(toString('Inf'), 76) as b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.023809523809523808
toTypeName(a): Nullable(Decimal(76, 76))
b: ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(76, 76))
See also
toDecimal256OrDefault
Like toDecimal256
, this function converts an input value to a value of type Decimal(76, S) but returns the default value in case of an error.
Syntax
toDecimal256OrDefault(expr, S[, default])
Arguments
expr
— A String representation of a number. String.S
— Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.default
(optional) — The default value to return if parsing to typeDecimal256(S)
is unsuccessful. Decimal256(S).
Supported arguments:
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
Unsupported arguments:
- String representations of Float32/64 values
NaN
andInf
. - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrDefault('0xc0fe', 1);
.
An overflow can occur if the value of expr
exceeds the bounds of Decimal256
: ( -1 * 10^(76 - S), 1 * 10^(76 - S) )
.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an error.
Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: toDecimal256OrDefault(1.15, 2)
is equal to 1.14
because 1.15 * 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: toDecimal256OrDefault('1.15', 2) = 1.15
Returned value
- Value of type
Decimal(76, S)
if successful, otherwise returns the default value if passed or0
if not. Decimal256(S).
Examples
Query:
SELECT
toDecimal256OrDefault(toString(1/42), 76) AS a,
toTypeName(a),
toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)')) AS b,
toTypeName(b)
FORMAT Vertical;
Result:
Row 1:
──────
a: 0.023809523809523808
toTypeName(a): Decimal(76, 76)
b: -1
toTypeName(b): Decimal(76, 0)
See also
toString
Functions for converting between numbers, strings (but not fixed strings), dates, and dates with times. All these functions accept one argument.
When converting to or from a string, the value is formatted or parsed using the same rules as for the TabSeparated format (and almost all other text formats). If the string can’t be parsed, an exception is thrown and the request is canceled.
When converting dates to numbers or vice versa, the date corresponds to the number of days since the beginning of the Unix epoch. When converting dates with times to numbers or vice versa, the date with time corresponds to the number of seconds since the beginning of the Unix epoch.
The date and date-with-time formats for the toDate/toDateTime functions are defined as follows:
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date. This allows support for the common occurrence of writing toDate(unix_timestamp)
, which otherwise would be an error and would require writing the more cumbersome toDate(toDateTime(unix_timestamp))
.
Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.
Conversion between numeric types uses the same rules as assignments between different numeric types in C++.
Additionally, the toString function of the DateTime argument can take a second String argument containing the name of the time zone. Example: Asia/Yekaterinburg
In this case, the time is formatted according to the specified time zone.
Example
Query:
SELECT
now() AS ts,
time_zone,
toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10
Result:
┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast │ 2023-09-08 20:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belgrade │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Berlin │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bratislava │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Brussels │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bucharest │ 2023-09-08 22:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘
Also see the toUnixTimestamp
function.
toFixedString
Converts a String type argument to a FixedString(N) type (a string of fixed length N). If the string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.
Syntax
toFixedString(s, N)
Arguments
Returned value
- An N length fixed string of
s
. FixedString.
Example
Query:
SELECT toFixedString('foo', 8) AS s;
Result:
┌─s─────────────┐
│ foo\0\0\0\0\0 │
└───────────────┘
toStringCutToZero
Accepts a String or FixedString argument. Returns the String with the content truncated at the first zero byte found.
Syntax
toStringCutToZero(s)
Example
Query:
SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut;
Result:
┌─s─────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo │
└───────────────┴───────┘
Query:
SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut;
Result:
┌─s──────────┬─s_cut─┐
│ foo\0bar\0 │ foo │
└────────────┴───────┘
toDecimalString
Converts a numeric value to String with the number of fractional digits in the output specified by the user.
Syntax
toDecimalString(number, scale)
Arguments
number
— Value to be represented as String, Int, UInt, Float, Decimal,scale
— Number of fractional digits, UInt8.
Returned value
- Input value represented as String with given number of fractional digits (scale). The number is rounded up or down according to common arithmetic in case requested scale is smaller than original number's scale.
Example
Query:
SELECT toDecimalString(CAST('64.32', 'Float64'), 5);
Result:
┌toDecimalString(CAST('64.32', 'Float64'), 5)─┐
│ 64.32000 │
└─────────────────────────────────────────────┘
reinterpretAsUInt8
Performs byte reinterpretation by treating the input value as a value of type UInt8. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt8(x)
Parameters
Returned value
- Reinterpreted value
x
as UInt8. UInt8.
Example
Query:
SELECT
toInt8(257) AS x,
toTypeName(x),
reinterpretAsUInt8(x) AS res,
toTypeName(res);
Result:
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8 │ 1 │ UInt8 │
└───┴───────────────┴─────┴─────────────────┘
reinterpretAsUInt16
Performs byte reinterpretation by treating the input value as a value of type UInt16. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt16(x)
Parameters
x
: value to byte reinterpret as UInt16. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as UInt16. UInt16.
Example
Query:
SELECT
toUInt8(257) AS x,
toTypeName(x),
reinterpretAsUInt16(x) AS res,
toTypeName(res);
Result:
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8 │ 1 │ UInt16 │
└───┴───────────────┴─────┴─────────────────┘
reinterpretAsUInt32
Performs byte reinterpretation by treating the input value as a value of type UInt32. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt32(x)
Parameters
x
: value to byte reinterpret as UInt32. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as UInt32. UInt32.
Example
Query:
SELECT
toUInt16(257) AS x,
toTypeName(x),
reinterpretAsUInt32(x) AS res,
toTypeName(res)
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16 │ 257 │ UInt32 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsUInt64
Performs byte reinterpretation by treating the input value as a value of type UInt64. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt64(x)
Parameters
x
: value to byte reinterpret as UInt64. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as UInt64. UInt64.
Example
Query:
SELECT
toUInt32(257) AS x,
toTypeName(x),
reinterpretAsUInt64(x) AS res,
toTypeName(res)
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32 │ 257 │ UInt64 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsUInt128
Performs byte reinterpretation by treating the input value as a value of type UInt128. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt128(x)
Parameters
x
: value to byte reinterpret as UInt128. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as UInt128. UInt128.
Example
Query:
SELECT
toUInt64(257) AS x,
toTypeName(x),
reinterpretAsUInt128(x) AS res,
toTypeName(res)
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64 │ 257 │ UInt128 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsUInt256
Performs byte reinterpretation by treating the input value as a value of type UInt256. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsUInt256(x)
Parameters
x
: value to byte reinterpret as UInt256. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as UInt256. UInt256.
Example
Query:
SELECT
toUInt128(257) AS x,
toTypeName(x),
reinterpretAsUInt256(x) AS res,
toTypeName(res)
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128 │ 257 │ UInt256 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsInt8
Performs byte reinterpretation by treating the input value as a value of type Int8. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt8(x)
Parameters
Returned value
- Reinterpreted value
x
as Int8. Int8.
Example
Query:
SELECT
toUInt8(257) AS x,
toTypeName(x),
reinterpretAsInt8(x) AS res,
toTypeName(res);
Result:
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8 │ 1 │ Int8 │
└───┴───────────────┴─────┴─────────────────┘
reinterpretAsInt16
Performs byte reinterpretation by treating the input value as a value of type Int16. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt16(x)
Parameters
Returned value
- Reinterpreted value
x
as Int16. Int16.
Example
Query:
SELECT
toInt8(257) AS x,
toTypeName(x),
reinterpretAsInt16(x) AS res,
toTypeName(res);
Result:
┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8 │ 1 │ Int16 │
└───┴───────────────┴─────┴─────────────────┘
reinterpretAsInt32
Performs byte reinterpretation by treating the input value as a value of type Int32. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt32(x)
Parameters
Returned value
- Reinterpreted value
x
as Int32. Int32.
Example
Query:
SELECT
toInt16(257) AS x,
toTypeName(x),
reinterpretAsInt32(x) AS res,
toTypeName(res);
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16 │ 257 │ Int32 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsInt64
Performs byte reinterpretation by treating the input value as a value of type Int64. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt64(x)
Parameters
Returned value
- Reinterpreted value
x
as Int64. Int64.
Example
Query:
SELECT
toInt32(257) AS x,
toTypeName(x),
reinterpretAsInt64(x) AS res,
toTypeName(res);
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32 │ 257 │ Int64 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsInt128
Performs byte reinterpretation by treating the input value as a value of type Int128. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt128(x)
Parameters
x
: value to byte reinterpret as Int128. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as Int128. Int128.
Example
Query:
SELECT
toInt64(257) AS x,
toTypeName(x),
reinterpretAsInt128(x) AS res,
toTypeName(res);
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64 │ 257 │ Int128 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsInt256
Performs byte reinterpretation by treating the input value as a value of type Int256. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsInt256(x)
Parameters
x
: value to byte reinterpret as Int256. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Reinterpreted value
x
as Int256. Int256.
Example
Query:
SELECT
toInt128(257) AS x,
toTypeName(x),
reinterpretAsInt256(x) AS res,
toTypeName(res);
Result:
┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int128 │ 257 │ Int256 │
└─────┴───────────────┴─────┴─────────────────┘
reinterpretAsFloat32
Performs byte reinterpretation by treating the input value as a value of type Float32. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsFloat32(x)
Parameters
Returned value
- Reinterpreted value
x
as Float32. Float32.
Example
Query:
SELECT reinterpretAsUInt32(toFloat32(0.2)) as x, reinterpretAsFloat32(x);
Result:
┌──────────x─┬─reinterpretAsFloat32(x)─┐
│ 1045220557 │ 0.2 │
└────────────┴─────────────────────────┘
reinterpretAsFloat64
Performs byte reinterpretation by treating the input value as a value of type Float64. Unlike CAST
, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.
Syntax
reinterpretAsFloat64(x)
Parameters
Returned value
- Reinterpreted value
x
as Float64. Float64.
Example
Query:
SELECT reinterpretAsUInt64(toFloat64(0.2)) as x, reinterpretAsFloat64(x);
Result:
┌───────────────────x─┬─reinterpretAsFloat64(x)─┐
│ 4596373779694328218 │ 0.2 │
└─────────────────────┴─────────────────────────┘
reinterpretAsDate
Accepts a string, fixed string or numeric value and interprets the bytes as a number in host order (little endian). It returns a date from the interpreted number as the number of days since the beginning of the Unix Epoch.
Syntax
reinterpretAsDate(x)
Parameters
x
: number of days since the beginning of the Unix Epoch. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Date. Date.
Implementation details
If the provided string isn’t long enough, the function works as if the string is padded with the necessary number of null bytes. If the string is longer than needed, the extra bytes are ignored.
Example
Query:
SELECT reinterpretAsDate(65), reinterpretAsDate('A');
Result:
┌─reinterpretAsDate(65)─┬─reinterpretAsDate('A')─┐
│ 1970-03-07 │ 1970-03-07 │
└───────────────────────┴────────────────────────┘
reinterpretAsDateTime
These functions accept a string and interpret the bytes placed at the beginning of the string as a number in host order (little endian). Returns a date with time interpreted as the number of seconds since the beginning of the Unix Epoch.
Syntax
reinterpretAsDateTime(x)
Parameters
x
: number of seconds since the beginning of the Unix Epoch. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.
Returned value
- Date and Time. DateTime.
Implementation details
If the provided string isn’t long enough, the function works as if the string is padded with the necessary number of null bytes. If the string is longer than needed, the extra bytes are ignored.
Example
Query:
SELECT reinterpretAsDateTime(65), reinterpretAsDateTime('A');
Result:
┌─reinterpretAsDateTime(65)─┬─reinterpretAsDateTime('A')─┐
│ 1970-01-01 01:01:05 │ 1970-01-01 01:01:05 │
└───────────────────────────┴────────────────────────────┘
reinterpretAsString
This function accepts a number, date or date with time and returns a string containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a string that is one byte long.
Syntax
reinterpretAsString(x)
Parameters
Returned value
- String containing bytes representing
x
. String.
Example
Query:
SELECT
reinterpretAsString(toDateTime('1970-01-01 01:01:05')),
reinterpretAsString(toDate('1970-03-07'));
Result:
┌─reinterpretAsString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsString(toDate('1970-03-07'))─┐
│ A │ A │
└────────────────────────────────────────────────────────┴───────────────────────────────────────────┘
reinterpretAsFixedString
This function accepts a number, date or date with time and returns a FixedString containing bytes representing the corresponding value in host order (little endian). Null bytes are dropped from the end. For example, a UInt32 type value of 255 is a FixedString that is one byte long.
Syntax
reinterpretAsFixedString(x)
Parameters
Returned value
- Fixed string containing bytes representing
x
. FixedString.
Example
Query:
SELECT
reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05')),
reinterpretAsFixedString(toDate('1970-03-07'));
Result:
┌─reinterpretAsFixedString(toDateTime('1970-01-01 01:01:05'))─┬─reinterpretAsFixedString(toDate('1970-03-07'))─┐
│ A │ A │
└─────────────────────────────────────────────────────────────┴────────────────────────────────────────────────┘
reinterpretAsUUID
In addition to the UUID functions listed here, there is dedicated UUID function documentation.
Accepts a 16 byte string and returns a UUID containing bytes representing the corresponding value in network byte order (big-endian). If the string isn't long enough, the function works as if the string is padded with the necessary number of null bytes to the end. If the string is longer than 16 bytes, the extra bytes at the end are ignored.
Syntax
reinterpretAsUUID(fixed_string)
Arguments
fixed_string
— Big-endian byte string. FixedString.
Returned value
- The UUID type value. UUID.
Examples
String to UUID.
Query:
SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')));
Result:
┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│ 08090a0b-0c0d-0e0f-0001-020304050607 │
└───────────────────────────────────────────────────────────────────────┘
Going back and forth from String to UUID.
Query:
WITH
generateUUIDv4() AS uuid,
identity(lower(hex(reverse(reinterpretAsString(uuid))))) AS str,
reinterpretAsUUID(reverse(unhex(str))) AS uuid2
SELECT uuid = uuid2;
Result:
┌─equals(uuid, uuid2)─┐
│ 1 │
└─────────────────────┘
reinterpret
Uses the same source in-memory bytes sequence for x
value and reinterprets it to destination type.
Syntax
reinterpret(x, type)
Arguments
x
— Any type.type
— Destination type. String.
Returned value
- Destination type value.
Examples
Query:
SELECT reinterpret(toInt8(-1), 'UInt8') as int_to_uint,
reinterpret(toInt8(1), 'Float32') as int_to_float,
reinterpret('1', 'UInt32') as string_to_int;
Result:
┌─int_to_uint─┬─int_to_float─┬─string_to_int─┐
│ 255 │ 1e-45 │ 49 │
└─────────────┴──────────────┴───────────────┘
CAST
Converts an input value to the specified data type. Unlike the reinterpret function, CAST
tries to present the same value using the new data type. If the conversion can not be done then an exception is raised.
Several syntax variants are supported.
Syntax
CAST(x, T)
CAST(x AS t)
x::t
Arguments
x
— A value to convert. May be of any type.T
— The name of the target data type. String.t
— The target data type.
Returned value
- Converted value.
If the input value does not fit the bounds of the target type, the result overflows. For example, CAST(-1, 'UInt8')
returns 255
.
Examples
Query:
SELECT
CAST(toInt8(-1), 'UInt8') AS cast_int_to_uint,
CAST(1.5 AS Decimal(3,2)) AS cast_float_to_decimal,
'1'::Int32 AS cast_string_to_int;
Result:
┌─cast_int_to_uint─┬─cast_float_to_decimal─┬─cast_string_to_int─┐
│ 255 │ 1.50 │ 1 │
└──────────────────┴───────────────────────┴────────────────────┘
Query:
SELECT
'2016-06-15 23:00:00' AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string;
Result:
┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────────┘
Conversion to FixedString (N) only works for arguments of type String or FixedString.
Type conversion to Nullable and back is supported.
Example
Query:
SELECT toTypeName(x) FROM t_null;
Result:
┌─toTypeName(x)─┐
│ Int8 │
│ Int8 │
└───────────────┘
Query:
SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null;
Result:
┌─toTypeName(CAST(x, 'Nullable(UInt16)'))─┐
│ Nullable(UInt16) │
│ Nullable(UInt16) │
└─────────────────────────────────────────┘
See also
- cast_keep_nullable setting
accurateCast(x, T)
Converts x
to the T
data type.
The difference from cast is that accurateCast
does not allow overflow of numeric types during cast if type value x
does not fit the bounds of type T
. For example, accurateCast(-1, 'UInt8')
throws an exception.
Example
Query:
SELECT cast(-1, 'UInt8') as uint8;
Result:
┌─uint8─┐
│ 255 │
└───────┘
Query:
SELECT accurateCast(-1, 'UInt8') as uint8;
Result:
Code: 70. DB::Exception: Received from localhost:9000. DB::Exception: Value in column Int8 cannot be safely converted into type UInt8: While processing accurateCast(-1, 'UInt8') AS uint8.
accurateCastOrNull(x, T)
Converts input value x
to the specified data type T
. Always returns Nullable type and returns NULL if the cast value is not representable in the target type.
Syntax
accurateCastOrNull(x, T)
Arguments
x
— Input value.T
— The name of the returned data type.
Returned value
- The value, converted to the specified data type
T
.
Example
Query:
SELECT toTypeName(accurateCastOrNull(5, 'UInt8'));
Result:
┌─toTypeName(accurateCastOrNull(5, 'UInt8'))─┐
│ Nullable(UInt8) │
└────────────────────────────────────────────┘
Query:
SELECT
accurateCastOrNull(-1, 'UInt8') as uint8,
accurateCastOrNull(128, 'Int8') as int8,
accurateCastOrNull('Test', 'FixedString(2)') as fixed_string;
Result:
┌─uint8─┬─int8─┬─fixed_string─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└───────┴──────┴──────────────┘
accurateCastOrDefault(x, T[, default_value])
Converts input value x
to the specified data type T
. Returns default type value or default_value
if specified if the cast value is not representable in the target type.
Syntax
accurateCastOrDefault(x, T)
Arguments
x
— Input value.T
— The name of the returned data type.default_value
— Default value of returned data type.
Returned value
- The value converted to the specified data type
T
.
Example
Query:
SELECT toTypeName(accurateCastOrDefault(5, 'UInt8'));
Result:
┌─toTypeName(accurateCastOrDefault(5, 'UInt8'))─┐
│ UInt8 │
└───────────────────────────────────────────────┘
Query:
SELECT
accurateCastOrDefault(-1, 'UInt8') as uint8,
accurateCastOrDefault(-1, 'UInt8', 5) as uint8_default,
accurateCastOrDefault(128, 'Int8') as int8,
accurateCastOrDefault(128, 'Int8', 5) as int8_default,
accurateCastOrDefault('Test', 'FixedString(2)') as fixed_string,
accurateCastOrDefault('Test', 'FixedString(2)', 'Te') as fixed_string_default;
Result:
┌─uint8─┬─uint8_default─┬─int8─┬─int8_default─┬─fixed_string─┬─fixed_string_default─┐
│ 0 │ 5 │ 0 │ 5 │ │ Te │
└───────┴───────────────┴──────┴──────────────┴──────────────┴──────────────────────┘