- Conversions between numeric types (e.g., integers to floats)
- Conversions to and from string types
- Date and time conversions
- Boolean conversions
- Special type conversions (e.g., UUID, IPv4/IPv6)
ClickHouse function reference
toBool
Converts an input value to a value of type Bool. Throws an exception in case of an error. Syntax:expr— Expression returning a number or a string.
- Values of type (
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256). - Values of type (
Float32,Float64). - Strings “true” or “false” (case-insensitive).
- Returns true or false based on evaluation of the argument. Bool.
int_valueconverts an integer 1 to true.float_valueconverts a float 1.01 to true.string_valueconverts the string ‘true’ to true.case_insensitive_valueconverts the string ‘FALSE’ to false, demonstrating case-insensitivity.
toInt8
Converts an input value to a value of typeInt8. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt8('0xc0fe');.
- 8-bit integer value.
Int8.
- If the input value cannot be represented within the bounds of
Int8(-128 to 127), overflow or underflow of the result occurs. This is not considered an error. For example:SELECT toInt8(128) == -128;. - The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
int8_valueshows a direct conversion of an integer toInt8.float_to_int8demonstrates how the function truncates the decimal part.string_to_int8shows conversion from a string representation.
toInt8OrZero
Converts an input value to a value of typeInt8 but returns 0 if the conversion is unsuccessful.
Syntax:
expr(String, numeric, orNull): Value to convert.
- Integer value converted from the input.
- 0 if the conversion is unsuccessful.
Int8
Example:
Query:
123is successfully converted to 123- ‘abc’ cannot be converted, so 0 is returned
- NULL is converted to 0
toInt8OrNull
Converts an input value to a value of typeInt8, but returns NULL if the conversion is not possible.
Syntax:
expr(String, number, or NULL): Value to convert.
- An
Int8value if the conversion was successful. - NULL if the conversion was not possible.
123is successfully converted to Int8.'abc'cannot be converted, so NULL is returned.NULLinput results in NULL output.'42'is successfully parsed and converted to Int8.8.5is truncated to 8 (rounding towards zero).256is outside the range of Int8 (-128 to 127), so NULL is returned.
toInt8OrDefault
Converts an input value to a value of typeInt8, but returns the default value if parsing fails.
Syntax:
expr(numeric orString): Value to convert. Expression returning a number or a string representation of a number.default(Int8, optional): Default value to return if parsing fails.
- An 8-bit signed integer value if conversion is successful.
- The
defaultvalue if specified, or 0 if not, when conversion fails.
valid_intsuccessfully converts the string ‘8’ to the Int8 value 8.invalid_intfails to convert ‘guacamole’ to an Int8, so it returns the default value -1.
- The function uses rounding towards zero, truncating fractional digits.
- If the input value is outside the range of Int8 (-128 to 127), it will overflow or underflow without raising an error.
- The default value’s type should match the target Int8 type.
toInt16
Converts an input value to a value of typeInt16. Throws an exception in case of an error.
Syntax:
expr— Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
- String representations of
Float32/64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt16('0xc0fe');.
- 16-bit integer value.
Int16.
- If the input value cannot be represented within the bounds of
Int16(-32,768 to 32,767), overflow or underflow of the result occurs. This is not considered an error. For example:SELECT toInt16(32768) == -32768;. - The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
Int16. Note how the fractional part is truncated in the second conversion.
See also:
toInt16OrZero
Converts an input value to a value of typeInt16 but returns 0 if the conversion is unsuccessful.
Syntax:
x(String,Integer,Float): Value to convert.
- Integer value of type
Int16. - 0, if the conversion is unsuccessful.
taco_countsuccessfully converts the string ‘16’ to theInt16value 16.spice_levelreturns 0 because ‘spicy’ cannot be converted to anInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt16OrNull
Converts an input value to a value of typeInt16 but returns NULL if an error occurs during the conversion.
Syntax:
x(String,Integer,Float): Value to convert.
- Integer value of type
Int16if conversion is successful. - NULL if conversion fails.
- ‘16’ is successfully converted to Int16.
- ‘abc’ cannot be converted, so NULL is returned.
- ‘32768’ is out of range for Int16 (which is -32768 to 32767), so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures. It’s particularly handy when dealing with potentially dirty or unpredictable input data.
toInt16OrDefault
LiketoInt16, 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:
expr(numeric orString): Expression returning a number or a string representation of a number.default(Int16, optional): The default value to return if parsing to type Int16 is unsuccessful.
- 16-bit integer value if successful, otherwise returns the default value if passed or 0 if not.
Int16.
- For the valid input ‘-16’, the function returns -16.
- For the invalid input ‘abc’, it returns the default value -1.
- 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.
toInt16toInt16OrZerotoInt16OrNull
toInt32
Converts an input value to a value of typeInt32. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - 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;- 32-bit integer value.
Int32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt32OrZero
Converts an input value to a value of typeInt32 but returns 0 in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (
U)Int8/16/32/128/256.
- String representations of
Float32/64values, including NaN and Inf. - 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.- 32-bit integer value if successful, otherwise 0.
Int32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
-32is successfully converted toInt32.'abc'cannot be converted, so 0 is returned.
toInt32OrNull
Converts an input value to a value of typeInt32 but returns NULL in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- 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.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt32OrDefault
Converts an input value to a value of typeInt32 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:
expr(Expression /String) — Expression returning a number or a string representation of a number.default(Int32, optional) — The default value to return if parsing to type Int32 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- 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.
- ‘-32’ is successfully converted to -32
- ‘abc’ cannot be converted, so the default value -1 is returned
- ‘123.45’ is truncated to 123 (rounding towards zero)
- ‘0xc0fe’ is treated as an invalid hexadecimal string, so the default value -1 is returned
toInt64
Converts an input value to a value of typeInt64. Throws an exception in case of an error.
Syntax:
expr— Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type (
Float32/Float64).
- String representations of (
Float32/Float64) values, including NaN and Inf. - 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;- 64-bit integer value.
Int64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt64OrZero
Converts an input value to a value of typeInt64 but returns 0 in case of an error.
Syntax:
x(String, number, or date/time value): Value to convert.
- 64-bit integer value if conversion is successful.
- 0 if conversion fails.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt64OrNull
Converts an input value to a value of typeInt64, but returns NULL if the conversion is unsuccessful.
Syntax:
expr(Any): Value to convert. Can be any data type that can be converted toInt64.
- A value of type Nullable(
Int64). - NULL if the conversion was unsuccessful.
- ‘123’ is successfully converted to
Int64. - ‘12.3’ fails because it’s a float.
- ‘twelve’ fails because it’s not a numeric string.
- ‘9223372036854775807’ is the maximum value for
Int64. - ‘9223372036854775808’ overflows
Int64, so NULL is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt64OrDefault
Converts an input value to a value of typeInt64 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:
expr(Expression/String): Expression returning a number or a string representation of a number.default(Int64, optional): The default value to return if parsing to type Int64 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- 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.
- ‘-64’ is successfully converted to -64
- ‘abc’ cannot be converted, so the default value -1 is returned
- ‘9223372036854775808’ is too large for Int64, causing overflow, so the default -1 is returned
toInt128
Converts an input value to a value of typeInt128. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt128('0xc0fe');.
- 128-bit integer value.
Int128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.If the input value cannot be represented within the bounds of
Int128, the result over or under flows. This is not considered an error.Int128, including a negative integer, a negative float, and a string representation of a negative integer.
See also:
toInt128OrZero
Converts an input value to a value of type Int128 but returns 0 if the conversion is unsuccessful. Syntax:expr(Any): Value to convert. Can be any data type that can be converted toInt128.
- An
Int128value if conversion is successful. - 0 if the conversion is unsuccessful.
Int128 value, while ‘big taco’ cannot be converted, so 0 is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.If the input value is outside the range of
Int128 (-170141183460469231731687303715884105728 to 170141183460469231731687303715884105727), the result will overflow or underflow, which is not considered an error.toInt128OrNull
Converts an input value to a value of typeInt128 but returns NULL if an error occurs during the conversion.
Syntax:
x(Any type convertible toInt128): Value to convert.
- A value of type Nullable(
Int128). - NULL if the conversion was not successful.
- ‘128’ is successfully converted to Int128
- ‘hello’ cannot be converted, so NULL is returned
- ‘340282366920938463463374607431768211456’ is out of range for Int128, so NULL is returned
toInt128OrDefault
Converts an input value to a value of typeInt128 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:
expr(Expression /String): Expression returning a number or a string representation of a number.default(Int128, optional): The default value to return if parsing to type Int128 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - 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.- 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.
- ‘-128’ is successfully converted to Int128.
- ‘abc’ cannot be converted, so the default value -1 is returned.
- ‘1.23e45’ is too large for Int128, causing overflow, so the default value -1 is returned.
toInt256
Converts an input value to a value of typeInt256. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number. Expression.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toInt256('0xc0fe');.
- 256-bit integer value.
Int256.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.If the input value cannot be represented within the bounds of
Int256, the result over or under flows. This is not considered an error.toInt256OrZero
Converts an input value to a value of typeInt256 but returns 0 in case of an error.
Syntax:
x(String): Value to convert.
- Converted integer value, or 0 if the conversion failed.
- Type:
Int256.
'42'is successfully converted to the Int256 value 42.'extra spicy'cannot be converted to an integer, so 0 is returned.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toInt256OrNull
Converts an input value to a value of typeInt256 but returns NULL if an error occurs during the conversion.
Syntax:
x(Any): Value to convert. Can be any type that is convertible toInt256.
- A value of type
Nullable(Int256). - Returns NULL if the conversion was not successful.
- ‘256’ is successfully converted to
Int256. - ‘big taco’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potential conversion errors gracefully, without throwing exceptions. It’s particularly handy when dealing with user input or data from external sources where the format might not always be consistent.
toInt256OrDefault
Converts an input value to a value of typeInt256 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:
expr(Expression /String): Expression returning a number or a string representation of a number.default(Int256, optional): The default value to return if parsing to type Int256 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- 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.
toUInt8
Converts an input value to a value of typeUInt8. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt8('0xc0fe').
- 8-bit unsigned integer value.
UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.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.int_valueconverts the integer 8 toUInt8.float_valueconverts the float 8.8 toUInt8, truncating the decimal part.string_valueconverts the string ‘8’ toUInt8.
toUInt8OrZero
Converts an input value to a value of typeUInt8 but returns 0 in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrZero('0xc0fe');.
- 8-bit unsigned integer value if successful, otherwise 0.
UInt8.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.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.
- ‘42’ is successfully converted to UInt8.
- ‘abc’ is not a valid number, so 0 is returned.
- ‘256’ overflows UInt8 (max value 255), so 0 is returned.
toUInt8OrNull
Converts an input value to a value of typeUInt8 but returns NULL in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt8OrNull('0xc0fe');.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
valid_uint8successfully converts the string ‘8’ to UInt8.invalid_uint8returns NULL because ‘abc’ cannot be converted to UInt8.
toUInt8OrDefault
Converts an input value to a value of typeUInt8 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:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt8, optional): The default value to return if parsing to typeUInt8is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- 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.
- ‘8’ is successfully converted to UInt8.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘256’ overflows UInt8, so the default value 0 is returned.
toUInt16
Converts an input value to a value of typeUInt16. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
- String representations of
Float32/64 values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt16('0xc0fe');.
- 16-bit unsigned integer value.
UInt16.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.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;.int_valueshows direct conversion from an integer.float_valuedemonstrates truncation of fractional part.string_valueillustrates conversion from a string representation.
toUInt16OrZero
Converts an input value to a value of typeUInt16 but returns 0 if the conversion is unsuccessful.
Syntax:
expr(String, integer, or floating-point number): Value to convert.
UInt16value if conversion is successful.- 0 if the conversion is unsuccessful.
UInt16 value, while ‘extra_spicy’ cannot be converted and returns 0.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toUInt16OrNull
Converts an input value to a value of typeUInt16 but returns NULL if an error occurs during the conversion.
Syntax:
expr(String, integer, or floating-point number): Value to convert.
- A
UInt16value if conversion is successful. - NULL if the conversion fails.
- ‘16’ is successfully converted to
UInt16. - ‘taco’ cannot be converted, so NULL is returned.
This function is useful when you need to handle potential conversion errors gracefully, returning NULL instead of throwing an exception.
toUInt16OrDefault
LiketoUInt16, 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:
expr(String, numeric): Expression returning a number or a string representation of a number.default(UInt16, optional): The default value to return if parsing to type UInt16 is unsuccessful.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - 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.- 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.
- ‘16’ is successfully converted to UInt16.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘300’ is outside the range of UInt8 (0-255) but within UInt16, so it’s converted successfully.
toUInt32
Converts an input value to a value of typeUInt32. Throws an exception in case of an error.
Syntax:
expr(numeric or string): Expression returning a number or a string representation of a number. Expression.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
- String representations of
Float32/64values, including NaN and Inf. - 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;- 32-bit unsigned integer value.
UInt32.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
UInt32, which could represent a count of tacos in an order.
See also:
Arguments:
x(String): A String representation of a number.
- String representations of (
U)(Int8/Int16/Int32/Int128/Int256).
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrZero(‘0xc0fe’);.
- 32-bit unsigned integer value if successful, otherwise 0. (
UInt32).
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toUInt32OrNull
Converts an input value to a value of typeUInt32 but returns NULL in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- 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.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- ‘32’ is successfully converted to UInt32.
- ‘3.14159’ returns NULL as it’s a float.
- ‘0xDEADBEEF’ returns NULL as it’s a hexadecimal value.
- An empty string returns NULL.
toUInt32OrDefault
Converts an input value to a value of typeUInt32 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:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt32, optional): The default value to return if parsing to type UInt32 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.UInt32: 32-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
- 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.
- ‘32’ is successfully converted to 32
- ‘abc’ cannot be converted, so the default value 0 is returned
- ‘256.7’ is truncated to 256 (rounding towards zero)
toUInt64
Converts an input value to a value of typeUInt64. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values of type
Float32/64.
- String representations of
Float32/64 values, including NaN and Inf. - 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;- 64-bit unsigned integer value.
UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toUInt64OrZero
Converts an input value to a value of typeUInt64 but returns 0 in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of
UInt8,UInt16,UInt32,UInt128,UInt256,Int8,Int16,Int32,Int128,Int256.
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt64OrZero('0xc0fe');.
- 64-bit unsigned integer value if successful, otherwise 0.
UInt64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toUInt64OrNull
Converts an input value to a value of typeUInt64 but returns NULL if an error occurs during the conversion.
Syntax:
x(any): Value to convert. Can be any type that is convertible toUInt64.
- A
UInt64value if the conversion is successful. - NULL if the conversion fails.
- ‘42’ is successfully converted to
UInt64. - ‘abc’ cannot be converted, so NULL is returned.
- ‘9223372036854775808’ is too large for
UInt64, so NULL is returned.
toUInt64OrDefault
Converts an input value to a value of typeUInt64 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:
expr(Expression /String): Expression returning a number or a string representation of a number.default(UInt64, optional): The default value to return if parsing to type UInt64 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- 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.
toUInt128
Converts an input value to a value of typeUInt128. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type (
Float32/Float64).
- String representations of (
Float32/Float64) values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt128('0xc0fe');.
- 128-bit unsigned integer value.
UInt128.
If the input value cannot be represented within the bounds of
UInt128, the result over or under flows. This is not considered an error.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.UInt128:
toUInt128OrZero
Converts an input value to a value of typeUInt128 but returns 0 in case of an error.
Syntax:
expr(String): Expression returning a number or a string representation of a number.
- String representations of (
U)Int8/16/32/128/256.
- String representations of
Float32/64 values, including NaN and Inf. - 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.- 128-bit unsigned integer value if successful, otherwise 0.
UInt128.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
UInt128, while ‘guacamole’ returns 0 as it’s an invalid input.
See also:
toUInt128OrNull
Converts an input value to a value of typeUInt128 but returns NULL if an error occurs during the conversion.
Syntax:
expr(any): Value to convert. Can be any data type that can be converted toUInt128.
- A
UInt128value if the conversion was successful. - NULL if the conversion failed.
- The first value is successfully converted to the maximum UInt128 value.
- The second value ‘taco’ cannot be converted, so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without throwing exceptions. It’s particularly handy when dealing with user input or data from external sources where the format might not always be consistent.
toUInt128OrDefault
LiketoUInt128, 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:
expr(String, numeric): Expression returning a number or a string representation of a number.default(UInt128, optional): The default value to return if parsing to typeUInt128is unsuccessful.
- (
U)Int8/16/32/64/128/256 Float32/64- String representations of (
U)Int8/16/32/128/256
- String representations of
Float32/64values, including NaN and Inf - 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.- 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.
- ‘128’ is successfully converted to
UInt128 - ‘abc’ cannot be converted, so the default value 0 is returned
- Both results are of type
UInt128
toUInt256
Converts an input value to a value of typeUInt256. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type (
U)(Int8/Int16/Int32/Int64/Int128/Int256). - Values of type
Float32/Float64.
- String representations of
Float32/Float64values, including NaN and Inf. - String representations of binary and hexadecimal values, e.g.
SELECT toUInt256('0xc0fe');.
- 256-bit unsigned integer value.
UInt256.
If the input value cannot be represented within the bounds of
UInt256, the result over or under flows. This is not considered an error.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.UInt256.
See also:
toUInt256OrZero
Converts an input value to a value of typeUInt256 but returns 0 in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toUInt256OrZero('0xc0fe');.
- 256-bit unsigned integer value if successful, otherwise 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.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
- ‘256’ is successfully converted to UInt256.
- ‘abc’ is an invalid input, so it returns 0.
- ‘18446744073709551616’ (2^64) demonstrates overflow behavior, returning the value as-is since it’s within UInt256 range.
toUInt256OrNull
Converts an input value to a value of typeUInt256 but returns NULL in case of an error.
Syntax:
x(String): A String representation of a number.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- 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.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.toUInt256OrDefault
Converts an input value to a value of typeUInt256 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:
expr(Expression/String): Expression returning a number or a string representation of a number.default(UInt256, optional): The default value to return if parsing to type UInt256 is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.UInt256: 256-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not.
- 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.
- ‘256’ is successfully converted to UInt256.
- ‘abc’ cannot be converted, so the default value 0 is returned.
- ‘-256’ is outside the range of UInt256, so the default value 0 is returned.
toFloat32
Converts an input value to a value of typeFloat32. Throws an exception in case of an error.
Syntax:
expr— Expression returning a number or a string representation of a number.
- Values or string representations of type
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256 - Values of type
Float32,Float64 - String representations of
Float32,Float64, including NaN and Inf (case-insensitive)
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat32('0xc0fe');
- 32-bit floating point value. Type:
Float32.
aconverts a numeric literal toFloat32bconverts a string representation toFloat32cdemonstrates handling of special values like NaN
toFloat32OrZerotoFloat32OrNulltoFloat32OrDefault
toFloat32OrZero
Converts an input value to a value of typeFloat32 but returns 0 if an error occurs during the conversion.
Syntax:
x(String, integer, or floating-point number): Value to convert.
- A
Float32value if conversion is successful. - 0 if the conversion fails.
pisuccessfully converts the string ‘3.14159’ to aFloat32value.failed_conversionreturns 0 because ‘extra_spicy’ cannot be converted to aFloat32.
This function is particularly useful when dealing with potentially dirty data, as it allows queries to continue without throwing exceptions for invalid conversions.
toFloat32OrNull
Converts an input value to a value of typeFloat32 but returns NULL if an error occurs during the conversion.
Syntax:
x(String, integer, or floating-point number): Value to be converted.
- A
Float32value if conversion is successful. - NULL if the conversion fails.
- ‘42.7’ is successfully converted to a
Float32value. - ‘al pastor’ cannot be converted, so NULL is returned.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures.
expr(Any data type that can be converted toFloat32): Value to convert.default(Float32, optional): Value returned if the conversion fails. If not specified, 0 is returned.
- A
Float32value if the conversion succeeds. - The
defaultvalue (or 0 if not specified) if the conversion fails.
- ‘3.14159’ is successfully converted to a
Float32. - ‘not_a_number’ cannot be converted, so the default value 99.9 is returned.
toFloat64
Converts an input value to a value of typeFloat64. Throws an exception in case of an error.
Syntax:
expr(numeric orString): Expression returning a number or a string representation of a number.
- Values or string representations of type
UInt8,UInt16,UInt32,UInt64,UInt128,UInt256,Int8,Int16,Int32,Int64,Int128,Int256. - Values or string representations of type
Float32,Float64. - String representations of
Float32,Float64, including NaN and Inf (case-insensitive).
- String representations of binary and hexadecimal values, e.g.
SELECT toFloat64('0xc0fe');.
- 64-bit floating point value.
Float64.
The function uses rounding towards zero, meaning it truncates fractional digits of numbers.
toFloat64OrZero
Converts an input value to a value of typeFloat64 but returns 0 if an error occurs during the conversion.
Syntax:
x(String, number, or date/time value): Value to convert.
Float64value if conversion is successful.- 0 if conversion fails.
- Successful conversion of a valid float string
- Zero returned for an invalid input
- Special handling of ‘inf’ as a valid float value
The function uses rounding towards zero, truncating fractional digits for whole numbers.
toFloat64— Throws an exception on conversion error.toFloat64OrNull— ReturnsNULLon conversion error.
toFloat64OrNull
Converts an input value to a value of typeFloat64, but returns NULL if the conversion is unsuccessful.
Syntax:
x(String, number, or date/time value): Value to convert.
- A
Float64value if the conversion is successful. NULLif the conversion fails.
NULL instead of throwing an exception for invalid input.
See Also
toFloat64toFloat64OrZerotoFloat64OrDefault
toFloat64OrDefault
Converts an input value to a value of typeFloat64, but returns a default value if the conversion fails.
Syntax:
expr(Any data type that can be converted toFloat64): Value to convert.default(Float64, optional): Value returned if the conversion fails. If not specified, 0 is returned.
- A
Float64value if the conversion succeeds. - The
defaultvalue (or 0 if not specified) if the conversion fails.
- ‘pi’ is successfully converted to a
Float64. - ‘invalid_with_default’ fails to convert and returns the specified default of 99.9.
- ‘invalid_without_default’ fails to convert and returns 0 since no default was specified.
This function is useful when you want to handle potential conversion errors gracefully without causing query failures.
toDate
Converts an input value to the Date data type. Syntax:expr(Any): Value to convert. Can be any data type that can be converted to a date.
- A value of
Datetype.
- When converting from DateTime or DateTime64, it truncates the time component.
- When converting from a number, if it’s greater than or equal to 65536, it’s interpreted as a Unix timestamp (seconds since epoch).
- The function supports various string formats, including ISO 8601.
toDateOrZero
Converts an input value to a value of typeDate. This function behaves similarly to toDate, but returns the lower boundary of the Date type (1970-01-01) if an invalid argument is received.
Syntax:
expr— Value to be converted. Can be any data type that can be converted to [Date].
- A value of type [
Date]. - Returns 1970-01-01 if the argument cannot be parsed as a valid date.
valid_dateshows a correctly parsed dateinvalid_datereturns the minimum Date value because the input is not a valid date stringout_of_range_datealso returns the minimum Date value because the input is out of the valid range for dates
This function is particularly useful when you want to handle potentially invalid date inputs without causing query errors, defaulting to a known value (1970-01-01) for invalid inputs.
toDateOrNull
Converts the input value to the Date data type. This function is similar to toDate, but returnsNULL if the conversion is unsuccessful.
Syntax:
expr(Any): Value to be converted. Can be any data type that can be converted to a date.
- A value in the
Datedata type if conversion is successful. NULLif the conversion fails.
- ‘2023-09-15’ is successfully converted to a Date.
- ‘2023-09-31’ is an invalid date (September has 30 days), so it returns NULL.
- ‘not a date’ cannot be interpreted as a date, so it returns NULL.
toDateOrDefault
Converts an input value to a value of typeDate. If the conversion is unsuccessful, it returns a default value.
Syntax:
expr(Expression): Expression returning a date or a string representation of a date.default_value(Date, optional): The default value to return if parsing to Date is unsuccessful.
- A
Datevalue if the conversion is successful. - The specified default value if provided, or 1970-01-01 if not, when the conversion fails.
valid_datesuccessfully converts the string to a Date.invalid_datefails to convert and returns the default date (1970-01-01).invalid_date_with_defaultfails to convert but returns the specified default date.
toDateTime
Converts an input value toDateTime.
Syntax:
expr(String,Int,Date,DateTime): The value to convert.time_zone(String, optional): The time zone to use for the conversion.
- A date with time.
DateTime.
- If
expris a number, it’s interpreted as a Unix timestamp (number of seconds since the beginning of the Unix Epoch). - If
expris a string, it may be interpreted as a Unix timestamp or as a string representation of date / date with time. - Parsing of short numbers’ string representations (up to 4 digits) is explicitly disabled due to ambiguity.
toDateTimeOrZero
Converts an input value to a value of type DateTime but returns the lower boundary of DateTime (1970-01-01 00:00:00) if an invalid argument is received. Syntax:expr(String): Value to be converted.time_zone(String, optional): Timezone for the returned value.
- DateTime value if conversion is successful, otherwise 1970-01-01 00:00:00.
- Type:
DateTime.
valid_datetimeshows a correctly parsed DateTime.invalid_datetimeshows the result of an invalid input, which is the lower boundary of DateTime.
This function is useful when you want to ensure that invalid date strings don’t cause errors in your queries, instead defaulting to a known value.
toDateTimeOrNull
Converts an input value to a value of typeDateTime but returns NULL if an error occurs during the conversion.
Syntax:
expr(any): Value to be converted. Can be any data type that can be converted to DateTime.timezone(String, optional): Timezone for the returned value. If not specified, the server’s timezone is used.
- A value of
DateTimetype if the conversion is successful. - NULL if the conversion fails.
- ‘2023-09-15 14:30:00’ is successfully converted to a DateTime value.
- ‘invalid_date’ cannot be converted, so NULL is returned.
toDateTimeOrDefault
Converts an input value to a value of typeDateTime but returns the default value in case of an error. If no default value is passed then the lower boundary of DateTime is returned in case of an error.
Syntax:
expr(Expression /String): Expression returning a number or a string representation of a number.time_zone(String, optional): Timezone name for the returned value.default_value(DateTime, optional): The default value to return if parsing to type DateTime is unsuccessful.
- A date and time value if successful, otherwise returns the default value if passed or the lower boundary of DateTime if not.
DateTime.
valid_datetimesuccessfully converts the string to a DateTime value.invalid_datetimereturns the specified default value for an invalid input.
toDate32
Converts an input value to a value of typeDate32. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, its borders are taken into account.
Syntax:
expr(String,UInt32, orDate): The value to convert.
- A calendar date. Type:
Date32.
toDate32OrZero
Converts an input value to a value of typeDate32 but returns the minimum value of Date32 (1900-01-01) if an invalid argument is received.
Syntax:
expr(Any): Value to be converted. Can be any data type that can be converted to Date32.
- A [
Date32] value if the conversion is successful. - 1900-01-01 if the conversion fails.
- ‘2023-05-15’ is successfully converted to a Date32 value.
- ‘not a date’ is an invalid input, so it returns the minimum Date32 value.
- ‘1899-12-31’ is out of the valid range for Date32, so it also returns the minimum value.
toDate32OrNull
Converts an input value to a value of type Date32 but returns NULL if an invalid argument is received. Syntax:expr(String,DateTime,Date,Date32,Int32,UInt32): Expression returning a number or a string representation of a date. Expression.
- A Date32 value if the conversion was successful, otherwise NULL.
- Returns
Date32/Nullable(Date32).
- ‘1955-01-01’ is successfully converted to a Date32 value.
- ‘not a date’ cannot be converted, so NULL is returned.
toDate32OrDefault
Converts an input value to a value of typeDate32 but returns the default value in case of an error. If no default value is passed then the minimum value of Date32 (1900-01-01) is returned in case of an error.
Syntax:
expr(Expression/String): Expression returning a date or a string representation of a date.default(Date32, optional): The default value to return if parsing to type Date32 is unsuccessful.
- A
Date32value if successful, otherwise returns the default value if passed or 1900-01-01 if not.
valid_datesuccessfully converts ‘1930-01-01’ to a Date32 value.invalid_datefails to parse ‘not_a_date’, so it returns the specified default value ‘2020-01-01’.
If the input value is outside the range of Date32 (1900-01-01 to 2299-12-31), the function returns the lower or upper bound of the Date32 range. This is not considered an error.
toDateTime64
Converts an input value to a value of typeDateTime64.
Syntax:
expr(String,UInt32,Float, orDateTime): Expression returning a number or a string representation of a number.scale(numeric): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
- A calendar date and time of day, with sub-second precision.
DateTime64.
Without the decimal point, the value is still treated as Unix Timestamp in seconds:
toDateTime64OrZero
Converts an input value to a value of type DateTime64 but returns the minimum value of DateTime64 if an invalid argument is received. Syntax:expr(String,UInt32,Float,DateTime): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64: 1970-01-01 00:00:00.000.
DateTime64.
toDateTime64OrNull
Converts an input value to a value of typeDateTime64 but returns NULL if an invalid argument is received.
Syntax:
expr(String,Number): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.
- A calendar date and time of day, with sub-second precision, otherwise NULL.
DateTime64/Nullable(DateTime64).
toDateTime64OrDefault
Converts an input value to a value of typeDateTime64, but returns either the default value of DateTime64 or the provided default if an invalid argument is received.
Syntax:
expr(String,Expression): Expression returning a number or a string representation of a number.scale(UInt8): Tick size (precision): 10^-precision seconds. Valid range: [0 : 9].timezone(String, optional): Time zone of the specified DateTime64 object.default(DateTime64, optional): Default value to return if an invalid argument is received.
- A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64 or the default value if provided.
DateTime64.
toDateTime64OrDefault handles invalid input. When given an invalid date string, it returns the minimum DateTime64 value if no default is specified, or the provided default value if one is given.
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:expr(numeric orString): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.
- Value of type
Decimal(9, S). Decimal32(S).
- Supported arguments: Values or string representations of type (
U)Int8/16/32/64/128/256 andFloat32/64. - Unsupported arguments: String representations of
Float32/64 values NaN and Inf (case-insensitive), and binary/hexadecimal values (e.g., ‘0xc0fe’). - An overflow can occur if the value of
exprexceeds 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.
toDecimal32OrZero
Converts an input value to a value of type Decimal(9, S) but returns 0 if an error occurs during the conversion. Syntax:expr(String): Value to convert to Decimal(9, S).S(UInt8): Number of decimal places, from 0 to 9.
- A value of type Decimal(9, S) if the conversion is successful.
- 0 with S decimal places if the conversion fails.
- The string “-1.111” is successfully converted to a Decimal(9, 5).
- The string “not a number” cannot be converted, so it returns 0.00000.
toDecimal32OrNull
Converts an input value to a value of type Nullable(Decimal(9, S)) but returns NULL if an error occurs during the conversion. Syntax:expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: 0 to 9.
- A value of type Nullable(Decimal(9, S)) if the conversion is successful.
- NULL if the conversion fails.
- The function supports string representations of (U)Int8/16/32/128/256 and Float32/64 numbers.
- It does not support string representations of ‘NaN’, ‘Inf’, ‘-Inf’, or hexadecimal numbers.
- If the input value exceeds the range of Decimal32 (-1 * 10^(9 - S) to 1 * 10^(9 - S)), the result will overflow or underflow.
- Excess fractional digits are discarded (not rounded).
toDecimal32OrDefault
LiketoDecimal32, this function converts an input value to a value of type Decimal(9, S) 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:
expr(String, numeric): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have.default(Decimal32(S), optional): The default value to return if parsing to type Decimal32(S) is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal32OrDefault('0xc0fe', CAST('-1', 'Decimal32(0)'));.
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.- Value of type
Decimal(9, S)if successful, otherwise returns the default value if passed or 0 if not.
- 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.
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:expr(numeric orString): Expression returning a number or a string representation of a number. Expression.S(UInt8): Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
- Values or string representations of
Float32/64 values NaN and Inf (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.- Value of type Decimal(18, S). Decimal64(S).
toDecimal64OrZero
Converts an input value to a value of type Decimal(18, S) but returns 0 if an error occurs during the conversion. Syntax:expr(String): Value to convert to Decimal(18, S).S(UInt8): Number of decimal places, from 0 to 18.
- A value of type Decimal(18, S) if the conversion is successful.
- 0 with S decimal places if the conversion fails.
- The function handles string representations of numbers, including those with decimal points.
- It returns 0 for inputs that cannot be parsed as numbers, such as ‘not a number’ in the example.
- Be cautious with potential loss of precision when converting between different decimal scales.
toDecimal64OrNull
Converts an input value to a value of type Nullable(Decimal(18, S)) but returns NULL if the conversion fails. Syntax:expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: [0, 18].
- A value of type Nullable(Decimal(18, S)) if successful, otherwise NULL.
- If the input value cannot be represented within the bounds of Decimal64 (-1 * 10^(18 - S), 1 * 10^(18 - S)), the function returns NULL.
- Excessive digits in the fractional part are discarded (not rounded).
- String representations of Float32/64 values NaN and Inf are not supported and will result in NULL.
- String representations of binary and hexadecimal values (e.g., ‘0xc0fe’) are not supported and will result in NULL.
toDecimal64OrDefault
Converts an input value to a value of type Decimal(18, S) 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:expr(Expression/String): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have.default(Decimal64(S), optional): The default value to return if parsing to type Decimal64(S) is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values, including NaN and Inf.
- 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.- Value of type Decimal(18, S) if successful, otherwise returns the default value if passed or 0 if not.
Decimal64(S).
- 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.
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:expr(numeric orString): Expression returning a number or a string representation of a number. Expression.S(UInt8): Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
- Values or string representations of
Float32/64 values NaN and Inf (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.- Value of type Decimal(38, S). Decimal128(S).
toDecimal128OrZero
Converts an input value to a value of type Decimal(38, S) but returns 0 if an error occurs during the conversion. Syntax:expr(String): Value to convert to Decimal(38, S).S(UInt8): Scale, the number of decimal places. Range: [0, 38].
- A value of type
Decimal(38, S)if the conversion is successful. - 0 with S decimal places if the conversion fails.
Decimal(38, S)
Examples
Query:
- The function supports string representations of (U)Int8/16/32/64/128/256 and Float32/64.
- It does not support string representations of NaN, Inf, or binary/hexadecimal values (e.g., ‘0xc0fe’).
- If the input value exceeds Decimal128 bounds (-1 * 10^(38 - S), 1 * 10^(38 - S)), overflow occurs without raising an error.
- Excess fractional digits are discarded without rounding.
toDecimal128OrNull
Converts an input value to a value of type Nullable(Decimal(38, S)) but returns NULL if the conversion fails. Syntax:expr(String): A string representation of a number.S(UInt8): Scale, the number of decimal places. Range: 0 to 38.
- String representations of (U)Int8/16/32/64/128/256.
- String representations of Float32/64.
- String representations of NaN and Inf.
- Binary and hexadecimal representations (e.g., ‘0xc0fe’).
- A value of type Nullable(Decimal(38, S)) if successful, otherwise NULL.
Decimal128(S).
- If the input value exceeds the bounds of Decimal128 (-1 * 10^(38 - S), 1 * 10^(38 - S)), overflow occurs. This is not considered an error.
- Excess fractional digits are truncated (not rounded).
- Excess integer digits will lead to an error.
toDecimal128OrDefault
LiketoDecimal128, this function converts an input value to a value of type Decimal(38, S) 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:
expr(String): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have.default(Decimal128(S), optional): The default value to return if parsing to type Decimal128(S) is unsuccessful.
- String representations of type (U)Int8/16/32/64/128/256.
- String representations of type Float32/64.
- String representations of Float32/64 values NaN and Inf.
- 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.- Value of type
Decimal(38, S)if successful, otherwise returns the default value if passed or 0 if not.
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:expr(Expression): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.
- Values or string representations of type (
U)Int8/16/32/64/128/256. - Values or string representations of type
Float32/64.
- Values or string representations of
Float32/64 values NaN and Inf (case-insensitive). - String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256('0xc0fe', 1);.
- Value of type
Decimal256(S).
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.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.toDecimal256OrZero
Converts an input value to a value of type Decimal(76, S) but returns 0 if an error occurs during the conversion. Syntax:expr(String): Value to convert to Decimal256.S(UInt8): Scale, the number of decimal places. Range: [0 : 76].
- Value of type Decimal(76, S) if successful, otherwise 0 with S decimal places.
Decimal256(S).
- ‘123.456’ is successfully converted to a Decimal256 with 3 decimal places.
- ‘not a number’ cannot be converted, so it returns 0.000.
- If the input value exceeds the range of Decimal256 (±1 * 10^(76 - S)), the result will overflow or underflow.
- Excess fractional digits are truncated, not rounded.
toDecimal256OrNull
Converts an input value to a value of type Nullable(Decimal(76, S)) but returns NULL in case of an error. Syntax:expr(String): A String representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.
- String representations of (U)Int8/16/32/128/256.
- String representations of Float32/64 values, including NaN and Inf.
- String representations of binary and hexadecimal values, e.g.
SELECT toDecimal256OrNull('0xc0fe', 1);.
- Value of type Nullable(Decimal(76, S)) if successful, otherwise NULL. Decimal256(S) / NULL.
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.The function uses rounding towards zero, meaning it truncates fractional digits of numbers.expr(String, numeric): Expression returning a number or a string representation of a number.S(UInt8): Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have.default(Decimal256(S), optional): The default value to return if parsing to type Decimal256(S) is unsuccessful.
- Values or string representations of type (U)Int8/16/32/64/128/256.
- Values of type Float32/64.
- String representations of Float32/64 values NaN and Inf.
- 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.- Value of type
Decimal256(S)if successful, otherwise returns the default value if passed or 0 if not.
toString
Converts an input value to a String. Syntax:x(any data type): Value to convert.
- A string representation of the input value.
- When converting dates and times, the function uses the same format as the TabSeparated output format.
- For DateTime values, an optional second argument can specify the timezone for formatting.
- When converting numbers to strings, the function uses the minimum number of characters required to represent the number.
toFixedString, CAST
toFixedString
Converts a String type argument to a FixedString(N) type (a string of fixed length N). Syntax:s(String): The input string to convert.N(UInt8): The desired fixed length of the resulting string.
- A fixed-length string of type FixedString(N).
Be cautious when using this function, as it can lead to data truncation if the input string is longer than the specified length N.
toStringCutToZero
Accepts a String or FixedString argument and returns the String with the content truncated at the first zero byte found. Syntax:s(StringorFixedString): The input string.
- A string truncated at the first zero byte.
String
toStringCutToZero truncates the string ‘salsa verde\0guacamole’ at the null byte, returning only ‘salsa verde’.
toDecimalString
Converts a numeric value to a String with the specified number of fractional digits. Syntax:number(numeric): Value to be represented as String. Can beInt,UInt,Float, orDecimal.scale(UInt8): Number of fractional digits.- Maximum scale for
DecimalandInt/UInttypes is 77. - Maximum scale for
Floattypes is 60.
- Maximum scale for
- A
Stringrepresentation of the input value with the specified number of fractional digits.
- The number is rounded up or down according to common arithmetic rules if the requested scale is smaller than the original number’s scale.
Float64 value representing a taco price to a string with 5 decimal places.
reinterpretAsUInt8
Performs byte reinterpretation by treating the input value as a value of typeUInt8. 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:
x(UInt8,Int8,UInt16,Int16,UInt32,Int32,UInt64,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asUInt8.
- Reinterpreted value
xasUInt8. Type:UInt8.
toInt8(257)results in 1 due to overflowreinterpretAsUInt8(x)reinterprets the Int8 value 1 as UInt8, which is also 1
Note that this function performs a low-level reinterpretation and should be used with caution, especially when dealing with negative numbers or values outside the UInt8 range.
reinterpretAsUInt16
Performs byte reinterpretation by treating the input value as a value of type UInt16. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt16.
- Reinterpreted value
xas UInt16. Type:UInt16.
reinterpretAsUInt32
Performs byte reinterpretation by treating the input value as a value of type UInt32. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt32.
- Reinterpreted value
xasUInt32. Type:UInt32.
reinterpretAsUInt64
Performs byte reinterpretation by treating the input value as a value of typeUInt64. 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asUInt64.
- Reinterpreted value
xasUInt64. Type:UInt64.
UInt32 value (which could represent a taco order number) as a UInt64:
UInt32 to UInt64 without changing its value, but changing its underlying representation.
reinterpretAsUInt128
Performs byte reinterpretation by treating the input value as a value of type UInt128. UnlikeCAST, 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:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt128.
- Reinterpreted value
xasUInt128. Type:UInt128.
reinterpretAsUInt256
Performs byte reinterpretation by treating the input value as a value of type UInt256. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,UInt128,Int8,Int16,Int32,Int64,Int128,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as UInt256.
- Reinterpreted value
xasUInt256. Type:UInt256.
reinterpretAsInt8
Performs byte reinterpretation by treating the input value as a value of type Int8. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Int8.
- Reinterpreted value
xas Int8. Type:Int8.
This function is useful for low-level data manipulation but should be used with caution as it can lead to unexpected results if not used correctly.
reinterpretAsInt16
Performs byte reinterpretation by treating the input value as a value of type Int16. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int16.
- Reinterpreted value
xas Int16. Type:Int16.
- We start with 257, which becomes 1 when converted to Int8 due to overflow.
- This Int8 value of 1 is then reinterpreted as an Int16, preserving the bit pattern but changing the type.
Note that this function performs a low-level reinterpretation and should be used with caution, as it can lead to unexpected results if not used correctly.
reinterpretAsInt32
Performs byte reinterpretation by treating the input value as a value of type Int32. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int32.
- Reinterpreted value
xas Int32. Type:Int32.
reinterpretAsInt64
Performs byte reinterpretation by treating the input value as a value of typeInt64. 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:
x(Int*,UInt*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret asInt64.
- Reinterpreted value
xasInt64. Type:Int64.
Int32 value of 257, then reinterpret it as an Int64. The numeric value remains the same, but the type changes to Int64.
This function performs a low-level reinterpretation of the bytes representing the input value. It does not perform type conversion in the conventional sense. Use with caution, as the results may be unexpected if you’re not familiar with the binary representation of different data types.
reinterpretAsInt128
Performs byte reinterpretation by treating the input value as a value of type Int128. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int128.
- Reinterpreted value
xas Int128. [Int128]
reinterpretAsInt256
Performs byte reinterpretation by treating the input value as a value of type Int256. UnlikeCAST, 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:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to byte reinterpret as Int256.
- Reinterpreted value
xas Int256. [Int256]
This function performs a low-level reinterpretation of the bytes representing the input value. It does not perform type conversion in the conventional sense. Use with caution, especially when dealing with floating-point numbers or other non-integer types.
reinterpretAsFloat32
Performs byte reinterpretation by treating the input value as a value of type Float32. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Float32.
- Reinterpreted value
xasFloat32.
This function is useful for low-level data manipulation but should be used with caution as it can produce unexpected results if not used correctly.
reinterpretAsFloat64
Performs byte reinterpretation by treating the input value as a value of type Float64. UnlikeCAST, 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:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Date,DateTime,UUID,String,FixedString): Value to reinterpret as Float64.
- Reinterpreted value
xasFloat64.
Be cautious when using this function, as it can lead to unexpected results if misused. It’s primarily useful for low-level data manipulation and should be used with care.
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:x(numeric,String,FixedString,Date,DateTime,UUID): Number of days since the beginning of the Unix Epoch.
- Date. (
Date).
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.
reinterpretAsDateTime
Performs byte reinterpretation by treating the input value as a value of type DateTime. UnlikeCAST, 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:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to reinterpret as DateTime.
- Reinterpreted value as
DateTime.
UInt32 value is reinterpreted as a DateTime, resulting in a specific date and time.
Example:
Query:
UInt32 value as a DateTime, perhaps representing the start of a grand taco festival. The result shows both the DateTime value and its string representation.
The reinterpretation of bytes can lead to unexpected results if not used carefully. Always ensure you understand the binary representation of your input data when using this function.
reinterpretAsString
Performs byte reinterpretation by treating the input value as a string. UnlikeCAST, this function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output may be meaningless.
Syntax:
x(UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,Float64,Date,DateTime,UUID,String,FixedString): Value to reinterpret as a string.
- A string containing bytes representing
x. Type:String.
- The
DateTimevalue is converted to a 4-byte string representation. - The
Datevalue is converted to a 2-byte string representation.
\0.
The function drops null bytes from the end. For example, a
UInt32 type value of 255 will result in a string that is one byte long.reinterpretAsFixedString
Performs byte reinterpretation by treating the input value as a value of type FixedString. UnlikeCAST, 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:
x(UInt*,Int*,Float*,Date,DateTime,UUID,String,FixedString): Value to reinterpret as FixedString.
- Reinterpreted value
xas FixedString.
- The number 42 is converted to
UInt16. - The resulting 2-byte value is reinterpreted as a FixedString(2).
- The output appears as a single asterisk because 42 in binary is
00101010, which isn’t a printable ASCII character.
The actual bytes stored in the FixedString may not be visible or may appear differently depending on the client’s character encoding.
reinterpretAsUUID
Converts a 16-byteFixedString to a UUID data type.
Syntax:
fixed_string(FixedString(16)): A 16-byte string in big-endian byte order.
- The UUID value. (
UUID)
- If the input string is shorter than 16 bytes, it’s padded with null bytes.
- If the input string is longer than 16 bytes, extra bytes are ignored.
reinterpret
Performs byte reinterpretation of the input value as a value of the specified type. UnlikeCAST, this function does not attempt to preserve the original value - if the target type cannot represent the input type, the output may be meaningless.
Syntax:
x(any type): Value to reinterpret.type(String): Target data type, specified as a string.
- The input value reinterpreted as the specified type.
- The negative integer -1 is reinterpreted as an unsigned 8-bit integer, resulting in 255.
- The integer 1 is reinterpreted as a 32-bit float, resulting in a very small number.
- The string ‘Taco’ is reinterpreted as a 32-bit unsigned integer, resulting in a seemingly random number.
Note that the results may vary depending on the system’s endianness and floating-point representation.
CAST
Converts an input value to the specified data type. Unlike thereinterpret function, CAST tries to present the same value using the new data type. If the conversion cannot be done, an exception is raised.
Syntax:
x- A value to convert. May be of any type.T(String) - The name of the target data type.t- The target data type.
- 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.FixedString(N) only works for arguments of type String or FixedString.
Type conversion to Nullable and back is supported.
Example:
accurateCast
Converts an input value to the specified data type. UnlikeCAST, this function does not allow overflow of numeric types during conversion.
Syntax:
x: Value to convert.T(String): The target data type (as a string).
- The value converted to the specified data type.
If the input value cannot be represented in the target type, This query will raise an error:Use
accurateCast throws an exception. For example:accurateCast when you need strict type conversion without silent overflow or truncation.accurateCastOrNull
Converts an input value to the specified data type, returning NULL if the conversion fails. Syntax:x: Input value to convert.T(String): The name of the target data type.
- The value converted to the specified data type
T, or NULL if the conversion is not possible.
5is successfully cast toUInt8-1cannot be cast toUInt8, so NULL is returned- ‘Carne Asada’ cannot be cast to
Int32, so NULL is returned
accurateCastOrDefault
Converts an input value to the specified data type, but returns a default value if the conversion fails. Syntax:x(Any): Value to convert.T(String): The target data type (as a string).default_value(Any, optional): Value to return if the conversion fails. If not specified, returns 0 or an empty value of the target type.
- The value converted to the specified data type
Tif successful. - The
default_valueif specified, or 0/empty value of typeTif the conversion fails.
- ‘123’ is successfully cast to Int32
- ‘abc’ fails to cast, so it returns 0 (default for Int32)
- ‘abc’ fails to cast, but returns -1 as specified
- The function uses rounding towards zero for numeric conversions.
- The default value’s type should match the target type
T.
toIntervalYear
Converts an input value to an interval of years. Syntax:n(numeric): Number of years. Can be any numeric type or a string representation of a number.
- An interval of
nyears. Type:IntervalYear.
toIntervalQuarter
Converts an input value to an interval of quarters of type IntervalQuarter. Syntax:n(Int32,UInt32,Int64,UInt64,Float32,Float64, orString): Number of quarters.
- An interval of
nquarters. (IntervalQuarter)
The
toIntervalQuarter function is particularly useful when you need to perform date arithmetic involving quarters, such as for financial reporting or seasonal analysis in your taco business data.toIntervalMonth
Converts an input value to an interval of months of type IntervalMonth. Syntax:n(Int32,UInt32,Float32, orString): Number of months. Accepts integer numbers, string representations of integers, or floating-point numbers.
- An interval of
nmonths. (IntervalMonth).
- We start with June 15, 2024
- Create an interval of 3 months
- Add the interval to the original date, resulting in September 15, 2024
toIntervalWeek
Converts an input value to an interval of weeks. Syntax:n(numeric): Number of weeks. Can be any of:Integer,Float, orStringrepresentation of a number.
- An interval of
nweeks. Type:IntervalWeek.
The
toIntervalWeek function is useful for date arithmetic operations, especially when working with weekly data or scheduling tasks.toIntervalDay
Converts an input value to an interval of days. Syntax:n(numeric): Number of days. Can be any of:- Integer number
- Float number
- String containing a number
- An interval of
ndays. Type:IntervalDay.
- We start with June 15, 2024
- Add an interval of 5 days
- The result is June 20, 2024
toIntervalHour
Converts an input value to an interval of hours. Syntax:n(numeric): Number of hours. Can be any of these types:Integer,Float, orStringrepresentation of a number.
- An interval of
nhours. Type:IntervalHour.
toIntervalMinute
Converts an input value to an interval of minutes. Syntax:n(Int32,UInt32,Int64,UInt64,Float32,Float64,Decimal(P, S),String) — Number of minutes. Integer numbers, string representations of integers, or floating-point numbers are accepted.
- An interval of
nminutes. (IntervalMinute)
- We start with June 15, 2024
- Add an interval of 30 minutes
- The result is June 15, 2024, at 00:30:00
toIntervalSecond
Converts an input value to an interval of seconds. Syntax:n(Int32,UInt32,Int64,UInt64,Float32,Float64,Decimal(P,S),String) — Number of seconds. Integer numbers, string representations of integers, or floating-point numbers are accepted.
- An interval of
nseconds. (IntervalSecond)
- We start with June 15, 2024
- Add an interval of 30 seconds
- The result is June 15, 2024, at 30 seconds past midnight
toIntervalMillisecond
Converts an input value to an interval of milliseconds. Syntax:n(numeric): Number of milliseconds. Integer, floating-point number, or string representation of a number.
- An interval of
nmilliseconds. Type:IntervalMillisecond.
toIntervalMillisecond function converts 500 to an interval, which is then added to the base time.
This function is part of ClickHouse’s interval functions, which are useful for date and time calculations. It allows for precise time adjustments at the millisecond level.
toIntervalMicrosecond
Converts an input value to an interval of microseconds of data type IntervalMicrosecond. Syntax:n(UInt*,Int*,Float*,String): Number of microseconds. Integer numbers or string representations thereof, and float numbers.
- Interval of n microseconds. IntervalMicrosecond.
- We start with a base time of June 15, 2024, at noon.
- We add an interval of 500,000 microseconds (0.5 seconds).
- The result shows the base time plus the interval, which is 0.5 seconds later.
toIntervalNanosecond
Converts an input value to an interval of nanoseconds of data type IntervalNanosecond. Syntax:n(UInt*,Int*,Float*,String): Number of nanoseconds. Integer numbers or string representations thereof, and float numbers.
- Interval of n nanoseconds. IntervalNanosecond.
parseDateTime
Converts a string to a DateTime value according to a specified format string. Syntax:- TO_TIMESTAMP
- str_to_date
str(String) — The string to be parsed.format(String) — The format string.timezone(String, optional) — Optional timezone name.
- A DateTime value parsed from the input string. (
DateTime)
%Y- Year (4 digits)%m- Month (01-12)%d- Day of month (01-31)%H- Hour (00-23)%i- Minutes (00-59)%s- Seconds (00-59)
formatDateTime.
If the timezone is not specified, the server’s timezone is used.
parseDateTimeOrZeroparseDateTimeOrNullparseDateTimeInJodaSyntax
parseDateTimeOrZero
Converts a string to a DateTime value according to a specified format. If the conversion fails, it returns a zero DateTime value (1970-01-01 00:00:00) instead of throwing an exception. Syntax:time_string(String): String containing a date and time to convert.format(String, optional): Format string. Default format is ‘%Y-%m-%d %H:%M:%S’.time_zone(String, optional): Time zone to use for parsing.
- DateTime value parsed from the input string.
- If parsing fails, returns 1970-01-01 00:00:00.
parseDateTimeOrNull
Converts a string to a DateTime value according to the specified format. This function is similar toparseDateTime, but returns NULL if the input string cannot be parsed.
Syntax:
time_string(String): String containing a date and time to convert.format(String, optional): Format string. Default format is ‘%Y-%m-%d %H:%M:%S’.time_zone(String, optional): Time zone for the returned value.
- A
DateTimevalue if the input string can be successfully parsed. NULLif the input string cannot be parsed.
NULL due to invalid format.
This function is useful when you need to handle potentially invalid date strings without causing query errors. It allows for more robust data processing in scenarios where date formats may be inconsistent or unreliable.
parseDateTimeInJodaSyntax
Parses a string into a DateTime value using a format string in Joda-Time syntax. Syntax:time_string(String): String containing a date and time to parse.format(String, optional): Format string in Joda-Time syntax. Default:'yyyy-MM-dd HH:mm:ss'.timezone(String, optional): Timezone for the returned value.
- Parsed DateTime value. (
DateTime)
- This function is the inverse of
formatDateTimeInJodaSyntax. - Supported format specifiers are similar to those in
formatDateTimeInJodaSyntax, except:S: fraction of second (not supported)z: time zone (not supported)Z: time zone offset/id (not supported)
formatDateTimeInJodaSyntaxparseDateTimeInJodaSyntaxOrZeroparseDateTimeInJodaSyntaxOrNull
parseDateTimeInJodaSyntaxOrZero
Converts a string to a DateTime value according to a format string in Joda syntax. Returns zero DateTime value (1970-01-01 00:00:00) if parsing fails. Syntax:time_string(String) — String containing a date and time to convert.format(String) — Format string in Joda syntax. Optional. Default:yyyy-MM-dd HH:mm:ss.time_zone(String) — Time zone. Optional.
- DateTime value parsed from the input string according to the specified format.
- 1970-01-01 00:00:00 if parsing fails.
DateTime
Example:
Query:
parseDateTimeInJodaSyntaxOrNull
Converts a string to a DateTime value according to the specified Joda-style format string. Returns NULL if parsing fails. Syntax:str(String): String containing a date and time to convert.format(String): Format string in Joda syntax.timezone(String, optional): Timezone for the returned value.
- DateTime value if parsing is successful, otherwise NULL. (
DateTime) / (Null)
parseDateTimeInJodaSyntax, but returns NULL instead of throwing an exception when parsing fails.
- The function supports most of the format specifiers from Joda-Time’s DateTimeFormat, but some advanced features like time zone handling (
z,Z) are not supported. - For handling time zones, use the optional
timezoneparameter instead.
- [parseDateTimeInJodaSyntax]
- [parseDateTimeInJodaSyntaxOrZero]
parseDateTimeBestEffort
Converts a date and time string to a DateTime data type using a best-effort approach. Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for parsing the string.
- A
DateTimevalue parsed from the input string.
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Unix timestamps (9-10 digit strings)
- Various common formats: YYYY-MM-DD hh:mm:ss, DD/MM/YYYY hh:mm:ss, YYYYMMDD, etc.
- Partial date strings: YYYY, YYYYMM, DD hh:mm, etc.
- Strings with time zone offsets: YYYY-MM-DD hh:mm:ss ±h:mm
- The function is more flexible but potentially slower than stricter parsing functions.
- If parsing fails, the function throws an exception.
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone. The function parses time_string according to the time zone.
time_stringconverted to theDateTime32data type.
If the year is not specified, it is considered to be equal to the current year. If the resulting DateTime happens to be in the future (even by a second after the current moment), then the current year is substituted by the previous year.
- [toDate]
- [toDateTime]
parseDateTimeBestEffortUS
Converts a date and time string to aDateTime data type, with a preference for US date formats in ambiguous cases.
Syntax:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for parsing the string.
- A
DateTimevalue.
parseDateTimeBestEffort for ISO date formats (e.g., YYYY-MM-DD hh:mm:ss) and other unambiguous formats. However, for ambiguous formats like MM/DD/YYYY or MM-DD-YY, it prefers the US date format (month before day).
As an exception, if the “month” value is greater than 12 and less than or equal to 31, the function reverts to the behavior of parseDateTimeBestEffort. For example, ‘15/08/2020’ is parsed as August 15, 2020.
Example:
Query:
- ‘02/03/2020’ is interpreted as February 3, 2020 (US format)
- ‘2020-02-03’ is unambiguous and parsed as February 3, 2020
- ‘15/08/2020’ is parsed as August 15, 2020 due to the exception rule
parseDateTimeBestEffortOrNull
Converts a string containing a date and time to a DateTime value, using heuristics to parse a wide variety of date/time formats. Returns NULL if it cannot parse the input string. Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for the returned value.
- A
DateTimevalue if the input can be parsed. NULLif the input cannot be parsed.
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrZero]
- [toDateTime]
parseDateTime32BestEffortOrNull
Converts a string containing a date and time toDateTime32 data type using a best-effort approach. Returns NULL if the conversion is unsuccessful.
Syntax:
time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
- A value of
DateTime32type if conversion is successful, otherwiseNULL.
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Common date and time representations
NULL instead of throwing an exception.
See Also
parseDateTimeBestEffortOrZero
Converts a string containing a date and time to a DateTime value, using a best-effort approach. If the conversion fails, it returns a zero DateTime value (1970-01-01 00:00:00) instead of throwing an exception. Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
- A
DateTimevalue if conversion is successful, otherwise 1970-01-01 00:00:00.
This function is particularly useful when dealing with potentially inconsistent date formats in your data, as it allows processing to continue without throwing exceptions for unparseable values.
parseDateTime32BestEffortOrZero
Converts a string containing a date and time to a DateTime32 value using a best-effort approach. If the conversion fails, it returns a zero date (1970-01-01 00:00:00). Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
- A
DateTime32value representing the parsed date and time, or 1970-01-01 00:00:00 if parsing fails.
- ISO 8601
- RFC 1123
- ClickHouse’s native format
- Common formats like ‘YYYY-MM-DD hh:mm:ss’, ‘DD/MM/YYYY hh:mm:ss’, etc.
- Unix timestamps
This function is particularly useful when dealing with potentially inconsistent date formats in your data, as it provides a fallback value rather than causing query failures.
parseDateTimeBestEffortUSOrNull
Converts a string containing a date and time to a DateTime value, preferring US date format (MM/DD/YYYY) in case of ambiguity. Returns NULL if the conversion is unsuccessful. Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone to use for parsing.
- A DateTime value if conversion is successful, otherwise NULL. [
DateTime] or [Null]
- ‘us_date’ is parsed as January 2, 2023
- ‘ambiguous_date’ is parsed as February 1, 2023 (preferring US format)
- ‘iso_date’ is parsed correctly regardless of format preference
- ‘invalid_date’ returns NULL as it cannot be parsed
- This function behaves similarly to
parseDateTimeBestEffort, but prefers US date format for ambiguous cases. - For unambiguous formats like ISO 8601, the function behaves the same as
parseDateTimeBestEffort. - The function supports various date and time formats, including Unix timestamps.
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrNull]
- [parseDateTimeBestEffortUS]
parseDateTimeBestEffortUSOrZero
Converts a string containing a date and time to a DateTime value, with a preference for US date formats in ambiguous cases. If the conversion fails, it returns a zero date (1970-01-01 00:00:00). Syntax:time_string(String): String containing a date and time to convert.time_zone(String, optional): Time zone for the returned value.
- A
DateTimevalue representing the parsed date and time. - Returns 1970-01-01 00:00:00 if parsing fails.
us_dateis parsed as February 10, 2023ambiguous_dateis parsed as October 2, 2023 (US format preferred)iso_dateis parsed correctly as February 10, 2023invalid_datereturns the zero date
- This function is particularly useful when working with data that may contain dates in US format (MM/DD/YYYY).
- For unambiguous date formats (like ISO 8601), it behaves similarly to
parseDateTimeBestEffort. - If the month value is greater than 12, it will be interpreted as day-first format.
- [parseDateTimeBestEffort]
- [parseDateTimeBestEffortOrNull]
- [toDateTime]
parseDateTime64BestEffort
Converts a string containing a date and time to aDateTime64 value, attempting to parse various date/time formats.
Syntax:
time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision of the result. Possible values: 3 (milliseconds), 6 (microseconds). Default: 3.timezone(String, optional): Timezone for the returned value.
- A
DateTime64value representing the parsed date and time.
The function uses the server’s timezone by default if not specified.
parseDateTime64BestEffortUS
Converts a string containing a date and time to aDateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. This function is similar to parseDateTime64BestEffort, but with special handling for US-style dates.
Syntax:
time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision for fractional seconds: 3 for milliseconds, 6 for microseconds. Default is 3.timezone(String, optional): Timezone for the returned value.
- A
DateTime64value representing the parsed date and time.
- For unambiguous formats like ISO 8601 (YYYY-MM-DD), the function behaves the same as
parseDateTime64BestEffort. - If the month value is greater than 12 and less than or equal to 31, it’s treated as a day, falling back to non-US interpretation.
- The function supports various date and time formats, including Unix timestamps and partial date strings.
parseDateTime64BestEffortparseDateTime64BestEffortUSOrNullparseDateTime64BestEffortUSOrZero
parseDateTime64BestEffortOrNull
Converts a string containing a date and time to aDateTime64 value, with support for parsing various date and time formats. If the input cannot be parsed, it returns NULL.
Syntax:
time_string(String): String containing a date or date with time to convert.precision(UInt8): Precision of the result (0-9). Default: 3 (milliseconds).timezone(String): Timezone for the returned value. Optional.
- A
DateTime64value if parsing is successful, otherwise NULL. (DateTime64orNull)
parseDateTime64BestEffortOrZero
Converts a string containing a date and time to a DateTime64 value, using a best-effort approach. If the conversion fails, it returns a zero value. Syntax:time_string(String): String containing a date and time to convert.precision(UInt8, optional): Precision of the result (0-9). Default: 3.timezone(String, optional): Timezone for the returned value.
- Parsed DateTime64 value, or 1970-01-01 00:00:00 if parsing fails. (
DateTime64)
- ISO 8601
- RFC 1123
- ClickHouse’s native formats
- Common formats like ‘YYYY-MM-DD hh:mm:ss’
parseDateTime64BestEffortUSOrNull
Converts a string containing a date and time to aDateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. Returns NULL if the conversion fails.
Syntax:
time_string(String): String containing a date and time to convert.precision(UInt8, optional): Precision of the result (0-9). Default is 3 (milliseconds).timezone(String, optional): Timezone for the returned value.
- A
DateTime64value if conversion is successful, otherwise NULL. [DateTime64] or [Null]
parseDateTime64BestEffortUSOrZero
Converts a string containing a date and time to a DateTime64 value, preferring US date format (MM/DD/YYYY) in ambiguous cases. Returns zero DateTime64 value (1970-01-01 00:00:00) if parsing fails. Syntax:time_string(String): String containing a date or date with time to convert.precision(UInt8, optional): Precision of the result (0-9). Default is 3 (milliseconds).timezone(String, optional): Timezone for the returned value.
- DateTime64 value parsed from the input string.
- 1970-01-01 00:00:00 (with specified precision) if parsing fails.
toLowCardinality
Converts an input value to the LowCardinality version of the same data type. Syntax:expr(supported data types): Expression resulting in one of the supported data types.
- The result of
exprconverted to the LowCardinality version of its type.
To convert data from the LowCardinality data type to its base type, use the Result:This function is particularly useful for optimizing storage and query performance for columns with a low number of distinct values, such as categories or statuses in a taco order system.
CAST function. For example:toUnixTimestamp64Milli
Converts a DateTime64 value to anInt64 value representing Unix timestamp with millisecond precision.
Syntax:
value(DateTime64): DateTime64 value with any precision.
- Unix timestamp with millisecond precision.
Int64.
The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
toUnixTimestamp64Micro
Converts a DateTime64 value to anInt64 value representing Unix timestamp with microsecond precision.
Syntax:
value(DateTime64): DateTime64 value with any precision.
- Unix timestamp with microsecond precision.
Int64.
- The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
- The function scales the input value up or down as needed, depending on its precision.
toUnixTimestamp64Nano
Converts a DateTime64 value to anInt64 value representing nanoseconds since the Unix epoch.
Syntax:
value(DateTime64): A DateTime64 value with any precision.
- The number of nanoseconds since the Unix epoch (1970-01-01 00:00:00 UTC). [
Int64]
The output value is a timestamp in UTC, not in the timezone of the input DateTime64.
fromUnixTimestamp64Milli
Converts anInt64 Unix timestamp in milliseconds to a DateTime64 value with millisecond precision.
Syntax:
value(Int64): Unix timestamp in milliseconds.timezone(String, optional): Timezone name for the returned value.
- DateTime64 value with 3 decimal places (millisecond precision). [
DateTime64(3)]
The input value is treated as a UTC timestamp, not a timestamp in the specified (or default) timezone.
fromUnixTimestamp64Micro
Converts a 64-bit integer representing microseconds since the Unix epoch to aDateTime64 value with microsecond precision.
Syntax:
value(Int64): Unix timestamp in microseconds.timezone(String, optional): Timezone for the returned value.
DateTime64value with 6 decimal places (microsecond precision).
The input value is treated as a UTC timestamp, not a timestamp in the specified (or default) timezone.
fromUnixTimestamp64Nano
Converts a 64-bit integer representing nanoseconds since the Unix epoch to aDateTime64 value with nanosecond precision.
Syntax:
value(Int64): Unix timestamp in nanoseconds.timezone(String, optional): Timezone for the returned value.
DateTime64value with nanosecond (9 decimal places) precision.
- The input
valueis treated as a UTC timestamp, not a timestamp in the given (or implicit) timezone. - If no timezone is specified, the server’s timezone is used.
DateTime64 value with nanosecond precision in the UTC timezone.
formatRow
Converts arbitrary expressions into a string using the specified format. Syntax:format(String) — Text format (e.g. CSV, TSV).x,y, … (any type) — Expressions to be formatted.
Only row-based formats are supported in this function.
formatRowNoNewline
Converts arbitrary expressions into a string via given format. Similar toformatRow, but trims the last newline character if present.
Syntax:
format(String): Text format. For example, CSV, TSV.x,y, … (any): Expressions to be formatted.
- A formatted string without a trailing newline.
Only row-based formats are supported in this function.