Type conversion functions¶

Common Issues with Data Conversion¶

to<type> functions and cast behave differently in some cases, for example in case of LowCardinality: cast removes LowCardinality trait to<type> functions don't. The same with Nullable, this behaviour is not compatible with SQL standard, and it can be changed using cast_keep_nullable setting.

Example:

SELECT
    toTypeName(toLowCardinality('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type────────────┬─to_type_result_type────┬─cast_result_type─┐
│ LowCardinality(String) │ LowCardinality(String) │ String           │
└────────────────────────┴────────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ String           │
└──────────────────┴─────────────────────┴──────────────────┘

SELECT
    toTypeName(toNullable('') AS val) AS source_type,
    toTypeName(toString(val)) AS to_type_result_type,
    toTypeName(CAST(val, 'String')) AS cast_result_type
SETTINGS cast_keep_nullable = 1

┌─source_type──────┬─to_type_result_type─┬─cast_result_type─┐
│ Nullable(String) │ Nullable(String)    │ Nullable(String) │
└──────────────────┴─────────────────────┴──────────────────┘

toBool¶

Converts an input value to a value of type Bool. Throws an exception in case of an error.

Syntax

toBool(expr)

Arguments

• expr: Expression returning a number or a string. Expression.

Supported arguments:

• Values of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.
• Strings true or false (case-insensitive).

Returned value

• Returns true or false based on evaluation of the argument. Bool.

Example

Query:

SELECT
    toBool(toUInt8(1)),
    toBool(toInt8(-1)),
    toBool(toFloat32(1.01)),
    toBool('true'),
    toBool('false'),
    toBool('FALSE')

Result:

toBool(toUInt8(1)):      true
toBool(toInt8(-1)):      true
toBool(toFloat32(1.01)): true
toBool('true'):          true
toBool('false'):         false
toBool('FALSE'):         false

toInt8¶

Converts an input value to a value of type Int8. Throws an exception in case of an error.

Syntax

toInt8(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt8('0xc0fe');.

Returned value

• 8-bit integer value. Int8.

Example

Query:

SELECT
    toInt8(-8),
    toInt8(-8.8),
    toInt8('-8')

Result:

Row 1:
──────
toInt8(-8):   -8
toInt8(-8.8): -8
toInt8('-8'): -8

toInt8OrZero¶

Like toInt8, this function converts an input value to a value of type Int8 but returns 0 in case of an error.

Syntax

toInt8OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of ordinary Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt8OrZero('0xc0fe');.

Returned value

• 8-bit integer value if successful, otherwise 0. Int8.

Example

Query:

SELECT
    toInt8OrZero('-8'),
    toInt8OrZero('abc')

Result:

Row 1:
──────
toInt8OrZero('-8'):  -8
toInt8OrZero('abc'): 0

toInt8OrNull¶

Like toInt8, this function converts an input value to a value of type Int8 but returns NULL in case of an error.

Syntax

toInt8OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt8OrNull('0xc0fe');.

Returned value

• 8-bit integer value if successful, otherwise NULL. Int8 / NULL.

Example

Query:

SELECT
    toInt8OrNull('-8'),
    toInt8OrNull('abc')

Result:

Row 1:
──────
toInt8OrNull('-8'):  -8
toInt8OrNull('abc'): ᴺᵁᴸᴸ

toInt8OrDefault¶

Like toInt8, this function converts an input value to a value of type Int8 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt8OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int8 is unsuccessful. Int8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt8OrDefault('0xc0fe', CAST('-1', 'Int8'));.

Returned value

• 8-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int8.

Example

Query:

SELECT
    toInt8OrDefault('-8', CAST('-1', 'Int8')),
    toInt8OrDefault('abc', CAST('-1', 'Int8'))

Result:

Row 1:
──────
toInt8OrDefault('-8', CAST('-1', 'Int8')):  -8
toInt8OrDefault('abc', CAST('-1', 'Int8')): -1

toInt16¶

Converts an input value to a value of type Int16. Throws an exception in case of an error.

Syntax

toInt16(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt16('0xc0fe');.

Returned value

• 16-bit integer value. Int16.

Example

Query:

SELECT
    toInt16(-16),
    toInt16(-16.16),
    toInt16('-16')

Result:

Row 1:
──────
toInt16(-16):    -16
toInt16(-16.16): -16
toInt16('-16'):  -16

toInt16OrZero¶

Like toInt16, this function converts an input value to a value of type Int16 but returns 0 in case of an error.

Syntax

toInt16OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt16OrZero('0xc0fe');.

Returned value

• 16-bit integer value if successful, otherwise 0. Int16.

Example

Query:

SELECT
    toInt16OrZero('-16'),
    toInt16OrZero('abc')

Result:

Row 1:
──────
toInt16OrZero('-16'): -16
toInt16OrZero('abc'): 0

toInt16OrNull¶

Like toInt16, this function converts an input value to a value of type Int16 but returns NULL in case of an error.

Syntax

toInt16OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt16OrNull('0xc0fe');.

Returned value

• 16-bit integer value if successful, otherwise NULL. Int16 / NULL.

Example

Query:

SELECT
    toInt16OrNull('-16'),
    toInt16OrNull('abc')

Result:

Row 1:
──────
toInt16OrNull('-16'): -16
toInt16OrNull('abc'): ᴺᵁᴸᴸ

toInt16OrDefault¶

Like toInt16, this function converts an input value to a value of type Int16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt16OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int16 is unsuccessful. Int16.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt16OrDefault('0xc0fe', CAST('-1', 'Int16'));.

Returned value

• 16-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int16.

Example

Query:

SELECT
    toInt16OrDefault('-16', CAST('-1', 'Int16')),
    toInt16OrDefault('abc', CAST('-1', 'Int16'))

Result:

Row 1:
──────
toInt16OrDefault('-16', CAST('-1', 'Int16')): -16
toInt16OrDefault('abc', CAST('-1', 'Int16')): -1

toInt32¶

Converts an input value to a value of type Int32. Throws an exception in case of an error.

Syntax

toInt32(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32('0xc0fe');.

Returned value

• 32-bit integer value. Int32.

Example

Query:

SELECT
    toInt32(-32),
    toInt32(-32.32),
    toInt32('-32')

Result:

Row 1:
──────
toInt32(-32):    -32
toInt32(-32.32): -32
toInt32('-32'):  -32

toInt32OrZero¶

Like toInt32, this function converts an input value to a value of type Int32 but returns 0 in case of an error.

Syntax

toInt32OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrZero('0xc0fe');.

Returned value

• 32-bit integer value if successful, otherwise 0. Int32

Example

Query:

SELECT
    toInt32OrZero('-32'),
    toInt32OrZero('abc')

Result:

Row 1:
──────
toInt32OrZero('-32'): -32
toInt32OrZero('abc'): 0

toInt32OrNull¶

Like toInt32, this function converts an input value to a value of type Int32 but returns NULL in case of an error.

Syntax

toInt32OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrNull('0xc0fe');.

Returned value

• 32-bit integer value if successful, otherwise NULL. Int32 / NULL.

Example

Query:

SELECT
    toInt32OrNull('-32'),
    toInt32OrNull('abc')

Result:

Row 1:
──────
toInt32OrNull('-32'): -32
toInt32OrNull('abc'): ᴺᵁᴸᴸ

toInt32OrDefault¶

Like toInt32, this function converts an input value to a value of type Int32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt32OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int32 is unsuccessful. Int32.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt32OrDefault('0xc0fe', CAST('-1', 'Int32'));.

Returned value

• 32-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int32.

Example

Query:

SELECT
    toInt32OrDefault('-32', CAST('-1', 'Int32')),
    toInt32OrDefault('abc', CAST('-1', 'Int32'))

Result:

Row 1:
──────
toInt32OrDefault('-32', CAST('-1', 'Int32')): -32
toInt32OrDefault('abc', CAST('-1', 'Int32')): -1

toInt64¶

Converts an input value to a value of type Int64. Throws an exception in case of an error.

Syntax

toInt64(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported types:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64('0xc0fe');.

Returned value

• 64-bit integer value. Int64.

Example

Query:

SELECT
    toInt64(-64),
    toInt64(-64.64),
    toInt64('-64')

Result:

Row 1:
──────
toInt64(-64):    -64
toInt64(-64.64): -64
toInt64('-64'):  -64

toInt64OrZero¶

Like toInt64, this function converts an input value to a value of type Int64 but returns 0 in case of an error.

Syntax

toInt64OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64OrZero('0xc0fe');.

Returned value

• 64-bit integer value if successful, otherwise 0. Int64.

Example

Query:

SELECT
    toInt64OrZero('-64'),
    toInt64OrZero('abc')

Result:

Row 1:
──────
toInt64OrZero('-64'): -64
toInt64OrZero('abc'): 0

toInt64OrNull¶

Like toInt64, this function converts an input value to a value of type Int64 but returns NULL in case of an error.

Syntax

toInt64OrNull(x)

Arguments

• x: A String representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64OrNull('0xc0fe');.

Returned value

• 64-bit integer value if successful, otherwise NULL. Int64 / NULL.

Example

Query:

SELECT
    toInt64OrNull('-64'),
    toInt64OrNull('abc')

Result:

Row 1:
──────
toInt64OrNull('-64'): -64
toInt64OrNull('abc'): ᴺᵁᴸᴸ

toInt64OrDefault¶

Like toInt64, this function converts an input value to a value of type Int64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt64OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int64 is unsuccessful. Int64.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt64OrDefault('0xc0fe', CAST('-1', 'Int64'));.

Returned value

• 64-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int64.

Example

Query:

SELECT
    toInt64OrDefault('-64', CAST('-1', 'Int64')),
    toInt64OrDefault('abc', CAST('-1', 'Int64'))

Result:

Row 1:
──────
toInt64OrDefault('-64', CAST('-1', 'Int64')): -64
toInt64OrDefault('abc', CAST('-1', 'Int64')): -1

toInt128¶

Converts an input value to a value of type Int128. Throws an exception in case of an error.

Syntax

toInt128(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128('0xc0fe');.

Returned value

• 128-bit integer value. Int128.

Example

Query:

SELECT
    toInt128(-128),
    toInt128(-128.8),
    toInt128('-128')

Result:

Row 1:
──────
toInt128(-128):   -128
toInt128(-128.8): -128
toInt128('-128'): -128

toInt128OrZero¶

Like toInt128, this function converts an input value to a value of type Int128 but returns 0 in case of an error.

Syntax

toInt128OrZero(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128OrZero('0xc0fe');.

Returned value

• 128-bit integer value if successful, otherwise 0. Int128.

Example

Query:

SELECT
    toInt128OrZero('-128'),
    toInt128OrZero('abc')

Result:

Row 1:
──────
toInt128OrZero('-128'): -128
toInt128OrZero('abc'):  0

toInt128OrNull¶

Like toInt128, this function converts an input value to a value of type Int128 but returns NULL in case of an error.

Syntax

toInt128OrNull(x)

Arguments

• x: A String representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128OrNull('0xc0fe');.

Returned value

• 128-bit integer value if successful, otherwise NULL. Int128 / NULL.

Example

Query:

SELECT
    toInt128OrNull('-128'),
    toInt128OrNull('abc')

Result:

Row 1:
──────
toInt128OrNull('-128'): -128
toInt128OrNull('abc'):  ᴺᵁᴸᴸ

toInt128OrDefault¶

Like toInt128, this function converts an input value to a value of type Int128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt128OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int128 is unsuccessful. Int128.

Supported arguments:

• (U)Int8/16/32/64/128/256.
• Float32/64.
• String representations of (U)Int8/16/32/128/256.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt128OrDefault('0xc0fe', CAST('-1', 'Int128'));.

Returned value

• 128-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int128.

Example

Query:

SELECT
    toInt128OrDefault('-128', CAST('-1', 'Int128')),
    toInt128OrDefault('abc', CAST('-1', 'Int128'))

Result:

Row 1:
──────
toInt128OrDefault('-128', CAST('-1', 'Int128')): -128
toInt128OrDefault('abc', CAST('-1', 'Int128')):  -1

toInt256¶

Converts an input value to a value of type Int256. Throws an exception in case of an error.

Syntax

toInt256(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt256('0xc0fe');.

Returned value

• 256-bit integer value. Int256.

Example

Query:

SELECT
    toInt256(-256),
    toInt256(-256.256),
    toInt256('-256')

Result:

Row 1:
──────
toInt256(-256):     -256
toInt256(-256.256): -256
toInt256('-256'):   -256

toInt256OrZero¶

Like toInt256, this function converts an input value to a value of type Int256 but returns 0 in case of an error.

Syntax

toInt256OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt256OrZero('0xc0fe');.

Returned value

• 256-bit integer value if successful, otherwise 0. Int256.

Example

Query:

SELECT
    toInt256OrZero('-256'),
    toInt256OrZero('abc')

Result:

Row 1:
──────
toInt256OrZero('-256'): -256
toInt256OrZero('abc'):  0

toInt256OrNull¶

Like toInt256, this function converts an input value to a value of type Int256 but returns NULL in case of an error.

Syntax

toInt256OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toInt256OrNull('0xc0fe');.

Returned value

• 256-bit integer value if successful, otherwise NULL. Int256 / NULL.

Example

Query:

SELECT
    toInt256OrNull('-256'),
    toInt256OrNull('abc')

Result:

Row 1:
──────
toInt256OrNull('-256'): -256
toInt256OrNull('abc'):  ᴺᵁᴸᴸ

toInt256OrDefault¶

Like toInt256, this function converts an input value to a value of type Int256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toInt256OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Int256 is unsuccessful. Int256.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf
• String representations of binary and hexadecimal values, e.g. SELECT toInt256OrDefault('0xc0fe', CAST('-1', 'Int256'));

Returned value

• 256-bit integer value if successful, otherwise returns the default value if passed or 0 if not. Int256.

Example

Query:

SELECT
    toInt256OrDefault('-256', CAST('-1', 'Int256')),
    toInt256OrDefault('abc', CAST('-1', 'Int256'))

Result:

Row 1:
──────
toInt256OrDefault('-256', CAST('-1', 'Int256')): -256
toInt256OrDefault('abc', CAST('-1', 'Int256')):  -1

toUInt8¶

Converts an input value to a value of type UInt8. Throws an exception in case of an error.

Syntax

toUInt8(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8('0xc0fe');.

Returned value

• 8-bit unsigned integer value. UInt8.

Example

Query:

SELECT
    toUInt8(8),
    toUInt8(8.8),
    toUInt8('8')

Result:

Row 1:
──────
toUInt8(8):   8
toUInt8(8.8): 8
toUInt8('8'): 8

toUInt8OrZero¶

Like toUInt8, this function converts an input value to a value of type UInt8 but returns 0 in case of an error.

Syntax

toUInt8OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of ordinary Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrZero('0xc0fe');.

Returned value

• 8-bit unsigned integer value if successful, otherwise 0. UInt8.

Example

Query:

SELECT
    toUInt8OrZero('-8'),
    toUInt8OrZero('abc')

Result:

Row 1:
──────
toUInt8OrZero('-8'):  0
toUInt8OrZero('abc'): 0

toUInt8OrNull¶

Like toUInt8, this function converts an input value to a value of type UInt8 but returns NULL in case of an error.

Syntax

toUInt8OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrNull('0xc0fe');.

Returned value

• 8-bit unsigned integer value if successful, otherwise NULL. UInt8 / NULL.

Example

Query:

SELECT
    toUInt8OrNull('8'),
    toUInt8OrNull('abc')

Result:

Row 1:
──────
toUInt8OrNull('8'):   8
toUInt8OrNull('abc'): ᴺᵁᴸᴸ

toUInt8OrDefault¶

Like toUInt8, this function converts an input value to a value of type UInt8 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt8OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type UInt8 is unsuccessful. UInt8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt8OrDefault('0xc0fe', CAST('0', 'UInt8'));.

Returned value

• 8-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt8.

Example

Query:

SELECT
    toUInt8OrDefault('8', CAST('0', 'UInt8')),
    toUInt8OrDefault('abc', CAST('0', 'UInt8'))

Result:

Row 1:
──────
toUInt8OrDefault('8', CAST('0', 'UInt8')):   8
toUInt8OrDefault('abc', CAST('0', 'UInt8')): 0

toUInt16¶

Converts an input value to a value of type UInt16. Throws an exception in case of an error.

Syntax

toUInt16(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16('0xc0fe');.

Returned value

• 16-bit unsigned integer value. UInt16.

Example

Query:

SELECT
    toUInt16(16),
    toUInt16(16.16),
    toUInt16('16')

Result:

Row 1:
──────
toUInt16(16):    16
toUInt16(16.16): 16
toUInt16('16'):  16

toUInt16OrZero¶

Like toUInt16, this function converts an input value to a value of type UInt16 but returns 0 in case of an error.

Syntax

toUInt16OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrZero('0xc0fe');.

Returned value

• 16-bit unsigned integer value if successful, otherwise 0. UInt16.

Example

Query:

SELECT
    toUInt16OrZero('16'),
    toUInt16OrZero('abc')

Result:

Row 1:
──────
toUInt16OrZero('16'):  16
toUInt16OrZero('abc'): 0

toUInt16OrNull¶

Like toUInt16, this function converts an input value to a value of type UInt16 but returns NULL in case of an error.

Syntax

toUInt16OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrNull('0xc0fe');.

Returned value

• 16-bit unsigned integer value if successful, otherwise NULL. UInt16 / NULL.

Example

Query:

SELECT
    toUInt16OrNull('16'),
    toUInt16OrNull('abc')

Result:

Row 1:
──────
toUInt16OrNull('16'):  16
toUInt16OrNull('abc'): ᴺᵁᴸᴸ

toUInt16OrDefault¶

Like toUInt16, this function converts an input value to a value of type UInt16 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt16OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type UInt16 is unsuccessful. UInt16.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt16OrDefault('0xc0fe', CAST('0', 'UInt16'));.

Returned value

• 16-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt16.

Example

Query:

SELECT
    toUInt16OrDefault('16', CAST('0', 'UInt16')),
    toUInt16OrDefault('abc', CAST('0', 'UInt16'))

Result:

Row 1:
──────
toUInt16OrDefault('16', CAST('0', 'UInt16')):  16
toUInt16OrDefault('abc', CAST('0', 'UInt16')): 0

toUInt32¶

Converts an input value to a value of type UInt32. Throws an exception in case of an error.

Syntax

toUInt32(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32('0xc0fe');.

Returned value

• 32-bit unsigned integer value. UInt32.

Example

Query:

SELECT
    toUInt32(32),
    toUInt32(32.32),
    toUInt32('32')

Result:

Row 1:
──────
toUInt32(32):    32
toUInt32(32.32): 32
toUInt32('32'):  32

toUInt32OrZero¶

Like toUInt32, this function converts an input value to a value of type UInt32 but returns 0 in case of an error.

Syntax

toUInt32OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrZero('0xc0fe');.

Returned value

• 32-bit unsigned integer value if successful, otherwise 0. UInt32

Example

Query:

SELECT
    toUInt32OrZero('32'),
    toUInt32OrZero('abc')

Result:

Row 1:
──────
toUInt32OrZero('32'):  32
toUInt32OrZero('abc'): 0

toUInt32OrNull¶

Like toUInt32, this function converts an input value to a value of type UInt32 but returns NULL in case of an error.

Syntax

toUInt32OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrNull('0xc0fe');.

Returned value

• 32-bit unsigned integer value if successful, otherwise NULL. UInt32 / NULL.

Example

Query:

SELECT
    toUInt32OrNull('32'),
    toUInt32OrNull('abc')

Result:

Row 1:
──────
toUInt32OrNull('32'):  32
toUInt32OrNull('abc'): ᴺᵁᴸᴸ

toUInt32OrDefault¶

Like toUInt32, this function converts an input value to a value of type UInt32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt32OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type UInt32 is unsuccessful. UInt32.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt32OrDefault('0xc0fe', CAST('0', 'UInt32'));.

Returned value

• 32-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt32.

Example

Query:

SELECT
    toUInt32OrDefault('32', CAST('0', 'UInt32')),
    toUInt32OrDefault('abc', CAST('0', 'UInt32'))

Result:

Row 1:
──────
toUInt32OrDefault('32', CAST('0', 'UInt32')):  32
toUInt32OrDefault('abc', CAST('0', 'UInt32')): 0

toUInt64¶

Converts an input value to a value of type UInt64. Throws an exception in case of an error.

Syntax

toUInt64(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported types:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64('0xc0fe');.

Returned value

• 64-bit unsigned integer value. UInt64.

Example

Query:

SELECT
    toUInt64(64),
    toUInt64(64.64),
    toUInt64('64')

Result:

Row 1:
──────
toUInt64(64):    64
toUInt64(64.64): 64
toUInt64('64'):  64

toUInt64OrZero¶

Like toUInt64, this function converts an input value to a value of type UInt64 but returns 0 in case of an error.

Syntax

toUInt64OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrZero('0xc0fe');.

Returned value

• 64-bit unsigned integer value if successful, otherwise 0. UInt64.

Example

Query:

SELECT
    toUInt64OrZero('64'),
    toUInt64OrZero('abc')

Result:

Row 1:
──────
toUInt64OrZero('64'):  64
toUInt64OrZero('abc'): 0

toUInt64OrNull¶

Like toUInt64, this function converts an input value to a value of type UInt64 but returns NULL in case of an error.

Syntax

toUInt64OrNull(x)

Arguments

• x: A String representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrNull('0xc0fe');.

Returned value

• 64-bit unsigned integer value if successful, otherwise NULL. UInt64 / NULL.

Example

Query:

SELECT
    toUInt64OrNull('64'),
    toUInt64OrNull('abc')

Result:

Row 1:
──────
toUInt64OrNull('64'):  64
toUInt64OrNull('abc'): ᴺᵁᴸᴸ

toUInt64OrDefault¶

Like toUInt64, this function converts an input value to a value of type UInt64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt64OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• defauult (optional): The default value to return if parsing to type UInt64 is unsuccessful. UInt64.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt64OrDefault('0xc0fe', CAST('0', 'UInt64'));.

Returned value

• 64-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt64.

Example

Query:

SELECT
    toUInt64OrDefault('64', CAST('0', 'UInt64')),
    toUInt64OrDefault('abc', CAST('0', 'UInt64'))

Result:

Row 1:
──────
toUInt64OrDefault('64', CAST('0', 'UInt64')):  64
toUInt64OrDefault('abc', CAST('0', 'UInt64')): 0

toUInt128¶

Converts an input value to a value of type UInt128. Throws an exception in case of an error.

Syntax

toUInt128(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128('0xc0fe');.

Returned value

• 128-bit unsigned integer value. UInt128.

Example

Query:

SELECT
    toUInt128(128),
    toUInt128(128.8),
    toUInt128('128')

Result:

Row 1:
──────
toUInt128(128):   128
toUInt128(128.8): 128
toUInt128('128'): 128

toUInt128OrZero¶

Like toUInt128, this function converts an input value to a value of type UInt128 but returns 0 in case of an error.

Syntax

toUInt128OrZero(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrZero('0xc0fe');.

Returned value

• 128-bit unsigned integer value if successful, otherwise 0. UInt128.

Example

Query:

SELECT
    toUInt128OrZero('128'),
    toUInt128OrZero('abc')

Result:

Row 1:
──────
toUInt128OrZero('128'): 128
toUInt128OrZero('abc'): 0

toUInt128OrNull¶

Like toUInt128, this function converts an input value to a value of type UInt128 but returns NULL in case of an error.

Syntax

toUInt128OrNull(x)

Arguments

• x: A String representation of a number. Expression / String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrNull('0xc0fe');.

Returned value

• 128-bit unsigned integer value if successful, otherwise NULL. UInt128 / NULL.

Example

Query:

SELECT
    toUInt128OrNull('128'),
    toUInt128OrNull('abc')

Result:

Row 1:
──────
toUInt128OrNull('128'): 128
toUInt128OrNull('abc'): ᴺᵁᴸᴸ

toUInt128OrDefault¶

Like toUInt128, this function converts an input value to a value of type UInt128 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt128OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type UInt128 is unsuccessful. UInt128.

Supported arguments:

• (U)Int8/16/32/64/128/256.
• Float32/64.
• String representations of (U)Int8/16/32/128/256.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt128OrDefault('0xc0fe', CAST('0', 'UInt128'));.

Returned value

• 128-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt128.

Example

Query:

SELECT
    toUInt128OrDefault('128', CAST('0', 'UInt128')),
    toUInt128OrDefault('abc', CAST('0', 'UInt128'))

Result:

Row 1:
──────
toUInt128OrDefault('128', CAST('0', 'UInt128')): 128
toUInt128OrDefault('abc', CAST('0', 'UInt128')): 0

toUInt256¶

Converts an input value to a value of type UInt256. Throws an exception in case of an error.

Syntax

toUInt256(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256('0xc0fe');.

Returned value

• 256-bit unsigned integer value. Int256.

Example

Query:

SELECT
    toUInt256(256),
    toUInt256(256.256),
    toUInt256('256')

Result:

Row 1:
──────
toUInt256(256):     256
toUInt256(256.256): 256
toUInt256('256'):   256

toUInt256OrZero¶

Like toUInt256, this function converts an input value to a value of type UInt256 but returns 0 in case of an error.

Syntax

toUInt256OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return 0):

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrZero('0xc0fe');.

Returned value

• 256-bit unsigned integer value if successful, otherwise 0. UInt256.

Example

Query:

SELECT
    toUInt256OrZero('256'),
    toUInt256OrZero('abc')

Result:

Row 1:
──────
toUInt256OrZero('256'): 256
toUInt256OrZero('abc'): 0

toUInt256OrNull¶

Like toUInt256, this function converts an input value to a value of type UInt256 but returns NULL in case of an error.

Syntax

toUInt256OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256.

Unsupported arguments (return \N)

• String representations of Float32/64 values, including NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrNull('0xc0fe');.

Returned value

• 256-bit unsigned integer value if successful, otherwise NULL. UInt256 / NULL.

Example

Query:

SELECT
    toUInt256OrNull('256'),
    toUInt256OrNull('abc')

Result:

Row 1:
──────
toUInt256OrNull('256'): 256
toUInt256OrNull('abc'): ᴺᵁᴸᴸ

toUInt256OrDefault¶

Like toUInt256, this function converts an input value to a value of type UInt256 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toUInt256OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type UInt256 is unsuccessful. UInt256.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values of type Float32/64.

Arguments for which the default value is returned:

• String representations of Float32/64 values, including NaN and Inf
• String representations of binary and hexadecimal values, e.g. SELECT toUInt256OrDefault('0xc0fe', CAST('0', 'UInt256'));

Returned value

• 256-bit unsigned integer value if successful, otherwise returns the default value if passed or 0 if not. UInt256.

Example

Query:

SELECT
    toUInt256OrDefault('-256', CAST('0', 'UInt256')),
    toUInt256OrDefault('abc', CAST('0', 'UInt256'))

Result:

Row 1:
──────
toUInt256OrDefault('-256', CAST('0', 'UInt256')): 0
toUInt256OrDefault('abc', CAST('0', 'UInt256')):  0

toFloat32¶

Converts an input value to a value of type Float32. Throws an exception in case of an error.

Syntax

toFloat32(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values of type (U)Int8/16/32/64/128/256.
• String representations of (U)Int8/16/32/128/256.
• Values of type Float32/64, including NaN and Inf.
• String representations of Float32/64, including NaN and Inf (case-insensitive).

Unsupported arguments:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat32('0xc0fe');.

Returned value

• 32-bit floating point value. Float32.

Example

Query:

SELECT
    toFloat32(42.7),
    toFloat32('42.7'),
    toFloat32('NaN')

Result:

Row 1:
──────
toFloat32(42.7):   42.7
toFloat32('42.7'): 42.7
toFloat32('NaN'):  nan

toFloat32OrZero¶

Like toFloat32, this function converts an input value to a value of type Float32 but returns 0 in case of an error.

Syntax

toFloat32OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256, Float32/64.

Unsupported arguments (return 0):

• String representations of binary and hexadecimal values, e.g. SELECT toFloat32OrZero('0xc0fe');.

Returned value

• 32-bit Float value if successful, otherwise 0. Float32.

Example

Query:

SELECT
    toFloat32OrZero('42.7'),
    toFloat32OrZero('abc')

Result:

Row 1:
──────
toFloat32OrZero('42.7'): 42.7
toFloat32OrZero('abc'):  0

toFloat32OrNull¶

Like toFloat32, this function converts an input value to a value of type Float32 but returns NULL in case of an error.

Syntax

toFloat32OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256, Float32/64.

Unsupported arguments (return \N):

• String representations of binary and hexadecimal values, e.g. SELECT toFloat32OrNull('0xc0fe');.

Returned value

• 32-bit Float value if successful, otherwise \N. Float32.

Example

Query:

SELECT
    toFloat32OrNull('42.7'),
    toFloat32OrNull('abc')

Result:

Row 1:
──────
toFloat32OrNull('42.7'): 42.7
toFloat32OrNull('abc'):  ᴺᵁᴸᴸ

toFloat32OrDefault¶

Like toFloat32, this function converts an input value to a value of type Float32 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toFloat32OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Float32 is unsuccessful. Float32.

Supported arguments:

• Values of type (U)Int8/16/32/64/128/256.
• String representations of (U)Int8/16/32/128/256.
• Values of type Float32/64, including NaN and Inf.
• String representations of Float32/64, including NaN and Inf (case-insensitive).

Arguments for which the default value is returned:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat32OrDefault('0xc0fe', CAST('0', 'Float32'));.

Returned value

• 32-bit Float value if successful, otherwise returns the default value if passed or 0 if not. Float32.

Example

Query:

SELECT
    toFloat32OrDefault('8', CAST('0', 'Float32')),
    toFloat32OrDefault('abc', CAST('0', 'Float32'))

Result:

Row 1:
──────
toFloat32OrDefault('8', CAST('0', 'Float32')):   8
toFloat32OrDefault('abc', CAST('0', 'Float32')): 0

toFloat64¶

Converts an input value to a value of type Float64. Throws an exception in case of an error.

Syntax

toFloat64(expr)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.

Supported arguments:

• Values of type (U)Int8/16/32/64/128/256.
• String representations of (U)Int8/16/32/128/256.
• Values of type Float32/64, including NaN and Inf.
• String representations of type Float32/64, including NaN and Inf (case-insensitive).

Unsupported arguments:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat64('0xc0fe');.

Returned value

• 64-bit floating point value. Float64.

Example

Query:

SELECT
    toFloat64(42.7),
    toFloat64('42.7'),
    toFloat64('NaN')

Result:

Row 1:
──────
toFloat64(42.7):   42.7
toFloat64('42.7'): 42.7
toFloat64('NaN'):  nan

toFloat64OrZero¶

Like toFloat64, this function converts an input value to a value of type Float64 but returns 0 in case of an error.

Syntax

toFloat64OrZero(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256, Float32/64.

Unsupported arguments (return 0):

• String representations of binary and hexadecimal values, e.g. SELECT toFloat64OrZero('0xc0fe');.

Returned value

• 64-bit Float value if successful, otherwise 0. Float64.

Example

Query:

SELECT
    toFloat64OrZero('42.7'),
    toFloat64OrZero('abc')

Result:

Row 1:
──────
toFloat64OrZero('42.7'): 42.7
toFloat64OrZero('abc'):  0

toFloat64OrNull¶

Like toFloat64, this function converts an input value to a value of type Float64 but returns NULL in case of an error.

Syntax

toFloat64OrNull(x)

Arguments

• x: A String representation of a number. String.

Supported arguments:

• String representations of (U)Int8/16/32/128/256, Float32/64.

Unsupported arguments (return \N):

• String representations of binary and hexadecimal values, e.g. SELECT toFloat64OrNull('0xc0fe');.

Returned value

• 64-bit Float value if successful, otherwise \N. Float64.

Example

Query:

SELECT
    toFloat64OrNull('42.7'),
    toFloat64OrNull('abc')

Result:

Row 1:
──────
toFloat64OrNull('42.7'): 42.7
toFloat64OrNull('abc'):  ᴺᵁᴸᴸ

toFloat64OrDefault¶

Like toFloat64, this function converts an input value to a value of type Float64 but returns the default value in case of an error. If no default value is passed then 0 is returned in case of an error.

Syntax

toFloat64OrDefault(expr[, default])

Arguments

• expr: Expression returning a number or a string representation of a number. Expression / String.
• default (optional): The default value to return if parsing to type Float64 is unsuccessful. Float64.

Supported arguments:

• Values of type (U)Int8/16/32/64/128/256.
• String representations of (U)Int8/16/32/128/256.
• Values of type Float32/64, including NaN and Inf.
• String representations of Float32/64, including NaN and Inf (case-insensitive).

Arguments for which the default value is returned:

• String representations of binary and hexadecimal values, e.g. SELECT toFloat64OrDefault('0xc0fe', CAST('0', 'Float64'));.

Returned value

• 64-bit Float value if successful, otherwise returns the default value if passed or 0 if not. Float64.

Example

Query:

SELECT
    toFloat64OrDefault('8', CAST('0', 'Float64')),
    toFloat64OrDefault('abc', CAST('0', 'Float64'))

Result:

Row 1:
──────
toFloat64OrDefault('8', CAST('0', 'Float64')):   8
toFloat64OrDefault('abc', CAST('0', 'Float64')): 0

toDate¶

Converts the argument to Date data type.

If the argument is DateTime or DateTime64, it truncates it and leaves the date component of the DateTime:

SELECT
    now() AS x,
    toDate(x)

┌───────────────────x─┬─toDate(now())─┐
│ 2022-12-30 13:44:17 │    2022-12-30 │
└─────────────────────┴───────────────┘

If the argument is a String, it is parsed as Date or DateTime. If it was parsed as DateTime, the date component is being used:

SELECT
    toDate('2022-12-30') AS x,
    toTypeName(x)

┌──────────x─┬─toTypeName(toDate('2022-12-30'))─┐
│ 2022-12-30 │ Date                             │
└────────────┴──────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

SELECT
    toDate('2022-12-30 01:02:03') AS x,
    toTypeName(x)

┌──────────x─┬─toTypeName(toDate('2022-12-30 01:02:03'))─┐
│ 2022-12-30 │ Date                                      │
└────────────┴───────────────────────────────────────────┘

If the argument is a number and looks like a UNIX timestamp (is greater than 65535), it is interpreted as a DateTime, then truncated to Date in the current timezone. The timezone argument can be specified as a second argument of the function. The truncation to Date depends on the timezone:

SELECT
    now() AS current_time,
    toUnixTimestamp(current_time) AS ts,
    toDateTime(ts) AS time_Amsterdam,
    toDateTime(ts, 'Pacific/Apia') AS time_Samoa,
    toDate(time_Amsterdam) AS date_Amsterdam,
    toDate(time_Samoa) AS date_Samoa,
    toDate(ts) AS date_Amsterdam_2,
    toDate(ts, 'Pacific/Apia') AS date_Samoa_2

Row 1:
──────
current_time:     2022-12-30 13:51:54
ts:               1672404714
time_Amsterdam:   2022-12-30 13:51:54
time_Samoa:       2022-12-31 01:51:54
date_Amsterdam:   2022-12-30
date_Samoa:       2022-12-31
date_Amsterdam_2: 2022-12-30
date_Samoa_2:     2022-12-31

The example above demonstrates how the same UNIX timestamp can be interpreted as different dates in different time zones.

If the argument is a number and it is smaller than 65536, it is interpreted as the number of days since 1970-01-01 (the first UNIX day) and converted to Date. It corresponds to the internal numeric representation of the Date data type. Example:

SELECT toDate(12345)

┌─toDate(12345)─┐
│    2003-10-20 │
└───────────────┘

This conversion does not depend on timezones.

If the argument does not fit in the range of the Date type, it results in an implementation-defined behavior, that can saturate to the maximum supported date or overflow:

SELECT toDate(10000000000.)

┌─toDate(10000000000.)─┐
│           2106-02-07 │
└──────────────────────┘

The function toDate can be also written in alternative forms:

SELECT
    now() AS time,
    toDate(time),
    DATE(time),
    CAST(time, 'Date')

┌────────────────time─┬─toDate(now())─┬─DATE(now())─┬─CAST(now(), 'Date')─┐
│ 2022-12-30 13:54:58 │    2022-12-30 │  2022-12-30 │          2022-12-30 │
└─────────────────────┴───────────────┴─────────────┴─────────────────────┘

toDateOrZero¶

The same as toDate but returns lower boundary of Date if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrZero('2022-12-30'), toDateOrZero('')

Result:

┌─toDateOrZero('2022-12-30')─┬─toDateOrZero('')─┐
│                 2022-12-30 │       1970-01-01 │
└────────────────────────────┴──────────────────┘

toDateOrNull¶

The same as toDate but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateOrNull('2022-12-30'), toDateOrNull('')

Result:

┌─toDateOrNull('2022-12-30')─┬─toDateOrNull('')─┐
│                 2022-12-30 │             ᴺᵁᴸᴸ │
└────────────────────────────┴──────────────────┘

toDateOrDefault¶

Like toDate but if unsuccessful, returns a default value which is either the second argument (if specified), or otherwise the lower boundary of Date.

Syntax

toDateOrDefault(expr [, default_value])

Example

Query:

SELECT toDateOrDefault('2022-12-30'), toDateOrDefault('', '2023-01-01'::Date)

Result:

┌─toDateOrDefault('2022-12-30')─┬─toDateOrDefault('', CAST('2023-01-01', 'Date'))─┐
│                    2022-12-30 │                                      2023-01-01 │
└───────────────────────────────┴─────────────────────────────────────────────────┘

toDateTime¶

Converts an input value to DateTime.

Syntax

toDateTime(expr[, time_zone ])

Arguments

• expr: The value. String, Int, Date or DateTime.
• time_zone: Time zone. String.

Returned value

• A date time. DateTime

Example

Query:

SELECT toDateTime('2022-12-30 13:44:17'), toDateTime(1685457500, 'UTC')

Result:

┌─toDateTime('2022-12-30 13:44:17')─┬─toDateTime(1685457500, 'UTC')─┐
│               2022-12-30 13:44:17 │           2023-05-30 14:38:20 │
└───────────────────────────────────┴───────────────────────────────┘

toDateTimeOrZero¶

The same as toDateTime but returns lower boundary of DateTime if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('')

Result:

┌─toDateTimeOrZero('2022-12-30 13:44:17')─┬─toDateTimeOrZero('')─┐
│                     2022-12-30 13:44:17 │  1970-01-01 00:00:00 │
└─────────────────────────────────────────┴──────────────────────┘

toDateTimeOrNull¶

The same as toDateTime but returns NULL if an invalid argument is received. Only String argument is supported.

Example

Query:

SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('')

Result:

┌─toDateTimeOrNull('2022-12-30 13:44:17')─┬─toDateTimeOrNull('')─┐
│                     2022-12-30 13:44:17 │                 ᴺᵁᴸᴸ │
└─────────────────────────────────────────┴──────────────────────┘

toDateTimeOrDefault¶

Like toDateTime but if unsuccessful, returns a default value which is either the third argument (if specified), or otherwise the lower boundary of DateTime.

Syntax

toDateTimeOrDefault(expr [, time_zone [, default_value]])

Example

Query:

SELECT toDateTimeOrDefault('2022-12-30 13:44:17'), toDateTimeOrDefault('', 'UTC', '2023-01-01'::DateTime('UTC'))

Result:

┌─toDateTimeOrDefault('2022-12-30 13:44:17')─┬─toDateTimeOrDefault('', 'UTC', CAST('2023-01-01', 'DateTime(\'UTC\')'))─┐
│                        2022-12-30 13:44:17 │                                                     2023-01-01 00:00:00 │
└────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘

toDate32¶

Converts the argument to the Date32 data type. If the value is outside the range, toDate32 returns the border values supported by Date32. If the argument has Date type, it's borders are taken into account.

Syntax

toDate32(expr)

Arguments

• expr: The value. String, UInt32 or Date.

Returned value

• A calendar date. Type Date32.

Example

1. The value is within the range:

SELECT toDate32('1955-01-01') AS value, toTypeName(value)

┌──────value─┬─toTypeName(toDate32('1925-01-01'))─┐
│ 1955-01-01 │ Date32                             │
└────────────┴────────────────────────────────────┘

2. The value is outside the range:

SELECT toDate32('1899-01-01') AS value, toTypeName(value)

┌──────value─┬─toTypeName(toDate32('1899-01-01'))─┐
│ 1900-01-01 │ Date32                             │
└────────────┴────────────────────────────────────┘

3. With Date argument:

SELECT toDate32(toDate('1899-01-01')) AS value, toTypeName(value)

┌──────value─┬─toTypeName(toDate32(toDate('1899-01-01')))─┐
│ 1970-01-01 │ Date32                                     │
└────────────┴────────────────────────────────────────────┘

toDate32OrZero¶

The same as toDate32 but returns the min value of Date32 if an invalid argument is received.

Example

Query:

SELECT toDate32OrZero('1899-01-01'), toDate32OrZero('')

Result:

┌─toDate32OrZero('1899-01-01')─┬─toDate32OrZero('')─┐
│                   1900-01-01 │         1900-01-01 │
└──────────────────────────────┴────────────────────┘

toDate32OrNull¶

The same as toDate32 but returns NULL if an invalid argument is received.

Example

Query:

SELECT toDate32OrNull('1955-01-01'), toDate32OrNull('')

Result:

┌─toDate32OrNull('1955-01-01')─┬─toDate32OrNull('')─┐
│                   1955-01-01 │               ᴺᵁᴸᴸ │
└──────────────────────────────┴────────────────────┘

toDate32OrDefault¶

Converts the argument to the Date32 data type. If the value is outside the range, toDate32OrDefault returns the lower border value supported by Date32. If the argument has Date type, it's borders are taken into account. Returns default value if an invalid argument is received.

Example

Query:

SELECT
    toDate32OrDefault('1930-01-01', toDate32('2020-01-01')),
    toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))

Result:

┌─toDate32OrDefault('1930-01-01', toDate32('2020-01-01'))─┬─toDate32OrDefault('xx1930-01-01', toDate32('2020-01-01'))─┐
│                                              1930-01-01 │                                                2020-01-01 │
└─────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘

toDateTime64¶

Converts an input value to a value of type DateTime64.

Syntax

toDateTime64(expr, scale, [timezone])

Arguments

• expr: The value. String, UInt32, Float or DateTime.
• scale - Tick size (precision): 10^-precision seconds. Valid range: [ 0 : 9 ].
• timezone (optional) - Time zone of the specified datetime64 object.

Returned value

• A calendar date and time of day, with sub-second precision. DateTime64.

Example

1. The value is within the range:

SELECT toDateTime64('1955-01-01 00:00:00.000', 3) AS value, toTypeName(value)

┌───────────────────value─┬─toTypeName(toDateTime64('1955-01-01 00:00:00.000', 3))─┐
│ 1955-01-01 00:00:00.000 │ DateTime64(3)                                          │
└─────────────────────────┴────────────────────────────────────────────────────────┘

2. As decimal with precision:

SELECT toDateTime64(1546300800.000, 3) AS value, toTypeName(value)

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800., 3))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3)                            │
└─────────────────────────┴──────────────────────────────────────────┘

Without the decimal point the value is still treated as Unix Timestamp in seconds:

SELECT toDateTime64(1546300800000, 3) AS value, toTypeName(value)

┌───────────────────value─┬─toTypeName(toDateTime64(1546300800000, 3))─┐
│ 2282-12-31 00:00:00.000 │ DateTime64(3)                              │
└─────────────────────────┴────────────────────────────────────────────┘

3. With timezone:

SELECT toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS value, toTypeName(value)

┌───────────────────value─┬─toTypeName(toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul'))─┐
│ 2019-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul')                                      │
└─────────────────────────┴─────────────────────────────────────────────────────────────────────┘

toDateTime64OrZero¶

Like toDateTime64, this function converts an input value to a value of type DateTime64 but returns the min value of DateTime64 if an invalid argument is received.

Syntax

toDateTime64OrZero(expr, scale, [timezone])

Arguments

• expr: The value. String, UInt32, Float or DateTime.
• scale - Tick size (precision): 10^-precision seconds. Valid range: [ 0 : 9 ].
• timezone (optional) - Time zone of the specified DateTime64 object.

Returned value

• A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64: 1970-01-01 01:00:00.000. DateTime64.

Example

Query:

SELECT toDateTime64OrZero('2008-10-12 00:00:00 00:30:30', 3) AS invalid_arg

Result:

┌─────────────invalid_arg─┐
│ 1970-01-01 01:00:00.000 │
└─────────────────────────┘

toDateTime64OrNull¶

Like toDateTime64, this function converts an input value to a value of type DateTime64 but returns NULL if an invalid argument is received.

Syntax

toDateTime64OrNull(expr, scale, [timezone])

Arguments

• expr: The value. String, UInt32, Float or DateTime.
• scale - Tick size (precision): 10^-precision seconds. Valid range: [ 0 : 9 ].
• timezone (optional) - Time zone of the specified DateTime64 object.

Returned value

• A calendar date and time of day, with sub-second precision, otherwise NULL. DateTime64/NULL.

Example

Query:

SELECT
    toDateTime64OrNull('1976-10-18 00:00:00.30', 3) AS valid_arg,
    toDateTime64OrNull('1976-10-18 00:00:00 30', 3) AS invalid_arg

Result:

┌───────────────valid_arg─┬─invalid_arg─┐
│ 1976-10-18 00:00:00.300 │        ᴺᵁᴸᴸ │
└─────────────────────────┴─────────────┘

toDateTime64OrDefault¶

Like toDateTime64, this function converts an input value to a value of type DateTime64, but returns either the default value of DateTime64 or the provided default if an invalid argument is received.

Syntax

toDateTime64OrNull(expr, scale, [timezone, default])

Arguments

• expr: The value. String, UInt32, Float or DateTime.
• scale - Tick size (precision): 10^-precision seconds. Valid range: [ 0 : 9 ].
• timezone (optional) - Time zone of the specified DateTime64 object.
• default (optional) - Default value to return if an invalid argument is received. DateTime64.

Returned value

• A calendar date and time of day, with sub-second precision, otherwise the minimum value of DateTime64 or the default value if provided. DateTime64.

Example

Query:

SELECT
    toDateTime64OrDefault('1976-10-18 00:00:00 30', 3) AS invalid_arg,
    toDateTime64OrDefault('1976-10-18 00:00:00 30', 3, 'UTC', toDateTime64('2001-01-01 00:00:00.00',3)) AS invalid_arg_with_default

Result:

┌─────────────invalid_arg─┬─invalid_arg_with_default─┐
│ 1970-01-01 01:00:00.000 │  2000-12-31 23:00:00.000 │
└─────────────────────────┴──────────────────────────┘

toDecimal32¶

Converts an input value to a value of type Decimal(9, S) with scale of S. Throws an exception in case of an error.

Syntax

toDecimal32(expr, S)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.
• S: Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values or string representations of type Float32/64.

Unsupported arguments:

• Values or string representations of Float32/64 values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal32('0xc0fe', 1);.

Returned value

• Value of type Decimal(9, S). Decimal32(S).

Example

Query:

SELECT
    toDecimal32(2, 1) AS a, toTypeName(a) AS type_a,
    toDecimal32(4.2, 2) AS b, toTypeName(b) AS type_b,
    toDecimal32('4.2', 3) AS c, toTypeName(c) AS type_c

Result:

Row 1:
──────
a:      2
type_a: Decimal(9, 1)
b:      4.2
type_b: Decimal(9, 2)
c:      4.2
type_c: Decimal(9, 3)

toDecimal32OrZero¶

Like toDecimal32, this function converts an input value to a value of type Decimal(9, S) but returns 0 in case of an error.

Syntax

toDecimal32OrZero(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal32OrZero('0xc0fe', 1);.

Returned value

• Value of type Decimal(9, S) if successful, otherwise 0 with S decimal places. Decimal32(S).

Example

Query:

SELECT
    toDecimal32OrZero(toString(-1.111), 5) AS a,
    toTypeName(a),
    toDecimal32OrZero(toString('Inf'), 5) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             -1.111
toTypeName(a): Decimal(9, 5)
b:             0
toTypeName(b): Decimal(9, 5)

toDecimal32OrNull¶

Like toDecimal32, this function converts an input value to a value of type Nullable(Decimal(9, S)) but returns 0 in case of an error.

Syntax

toDecimal32OrNull(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal32OrNull('0xc0fe', 1);.

Returned value

• Value of type Nullable(Decimal(9, S)) if successful, otherwise value NULL of the same type. Decimal32(S).

Examples

Query:

SELECT
    toDecimal32OrNull(toString(-1.111), 5) AS a,
    toTypeName(a),
    toDecimal32OrNull(toString('Inf'), 5) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             -1.111
toTypeName(a): Nullable(Decimal(9, 5))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(9, 5))

toDecimal32OrDefault¶

Like toDecimal32, this function converts an input value to a value of type Decimal(9, S) but returns the default value in case of an error.

Syntax

toDecimal32OrDefault(expr, S[, default])

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. UInt8.
• default (optional): The default value to return if parsing to type Decimal32(S) is unsuccessful. Decimal32(S).

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal32OrDefault('0xc0fe', 1);.

Returned value

• Value of type Decimal(9, S) if successful, otherwise returns the default value if passed or 0 if not. Decimal32(S).

Examples

Query:

SELECT
    toDecimal32OrDefault(toString(0.0001), 5) AS a,
    toTypeName(a),
    toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)')) AS b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(9, 5)
b:             -1
toTypeName(b): Decimal(9, 0)

toDecimal64¶

Converts an input value to a value of type Decimal(18, S) with scale of S. Throws an exception in case of an error.

Syntax

toDecimal64(expr, S)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.
• S: Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values or string representations of type Float32/64.

Unsupported arguments:

• Values or string representations of Float32/64 values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal64('0xc0fe', 1);.

Returned value

• Value of type Decimal(18, S). Decimal64(S).

Example

Query:

SELECT
    toDecimal64(2, 1) AS a, toTypeName(a) AS type_a,
    toDecimal64(4.2, 2) AS b, toTypeName(b) AS type_b,
    toDecimal64('4.2', 3) AS c, toTypeName(c) AS type_c

Result:

Row 1:
──────
a:      2
type_a: Decimal(18, 1)
b:      4.2
type_b: Decimal(18, 2)
c:      4.2
type_c: Decimal(18, 3)

toDecimal64OrZero¶

Like toDecimal64, this function converts an input value to a value of type Decimal(18, S) but returns 0 in case of an error.

Syntax

toDecimal64OrZero(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal64OrZero('0xc0fe', 1);.

Returned value

• Value of type Decimal(18, S) if successful, otherwise 0 with S decimal places. Decimal64(S).

Example

Query:

SELECT
    toDecimal64OrZero(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrZero(toString('Inf'), 18) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(18, 18)
b:             0
toTypeName(b): Decimal(18, 18)

toDecimal64OrNull¶

Like toDecimal64, this function converts an input value to a value of type Nullable(Decimal(18, S)) but returns 0 in case of an error.

Syntax

toDecimal64OrNull(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal64OrNull('0xc0fe', 1);.

Returned value

• Value of type Nullable(Decimal(18, S)) if successful, otherwise value NULL of the same type. Decimal64(S).

Examples

Query:

SELECT
    toDecimal64OrNull(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrNull(toString('Inf'), 18) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Nullable(Decimal(18, 18))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(18, 18))

toDecimal64OrDefault¶

Like toDecimal64, this function converts an input value to a value of type Decimal(18, S) but returns the default value in case of an error.

Syntax

toDecimal64OrDefault(expr, S[, default])

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 18, specifying how many digits the fractional part of a number can have. UInt8.
• default (optional): The default value to return if parsing to type Decimal64(S) is unsuccessful. Decimal64(S).

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal64OrDefault('0xc0fe', 1);.

Returned value

• Value of type Decimal(18, S) if successful, otherwise returns the default value if passed or 0 if not. Decimal64(S).

Examples

Query:

SELECT
    toDecimal64OrDefault(toString(0.0001), 18) AS a,
    toTypeName(a),
    toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)')) AS b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(18, 18)
b:             -1
toTypeName(b): Decimal(18, 0)

toDecimal128¶

Converts an input value to a value of type Decimal(38, S) with scale of S. Throws an exception in case of an error.

Syntax

toDecimal128(expr, S)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.
• S: Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values or string representations of type Float32/64.

Unsupported arguments:

• Values or string representations of Float32/64 values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal128('0xc0fe', 1);.

Returned value

• Value of type Decimal(38, S). Decimal128(S).

Example

Query:

SELECT
    toDecimal128(99, 1) AS a, toTypeName(a) AS type_a,
    toDecimal128(99.67, 2) AS b, toTypeName(b) AS type_b,
    toDecimal128('99.67', 3) AS c, toTypeName(c) AS type_c

Result:

Row 1:
──────
a:      99
type_a: Decimal(38, 1)
b:      99.67
type_b: Decimal(38, 2)
c:      99.67
type_c: Decimal(38, 3)

toDecimal128OrZero¶

Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns 0 in case of an error.

Syntax

toDecimal128OrZero(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrZero('0xc0fe', 1);.

Returned value

• Value of type Decimal(38, S) if successful, otherwise 0 with S decimal places. Decimal128(S).

Example

Query:

SELECT
    toDecimal128OrZero(toString(0.0001), 38) AS a,
    toTypeName(a),
    toDecimal128OrZero(toString('Inf'), 38) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(38, 38)
b:             0
toTypeName(b): Decimal(38, 38)

toDecimal128OrNull¶

Like toDecimal128, this function converts an input value to a value of type Nullable(Decimal(38, S)) but returns 0 in case of an error.

Syntax

toDecimal128OrNull(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrNull('0xc0fe', 1);.

Returned value

• Value of type Nullable(Decimal(38, S)) if successful, otherwise value NULL of the same type. Decimal128(S).

Examples

Query:

SELECT
    toDecimal128OrNull(toString(1/42), 38) AS a,
    toTypeName(a),
    toDecimal128OrNull(toString('Inf'), 38) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Nullable(Decimal(38, 38))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(38, 38))

toDecimal128OrDefault¶

Like toDecimal128, this function converts an input value to a value of type Decimal(38, S) but returns the default value in case of an error.

Syntax

toDecimal128OrDefault(expr, S[, default])

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 38, specifying how many digits the fractional part of a number can have. UInt8.
• default (optional): The default value to return if parsing to type Decimal128(S) is unsuccessful. Decimal128(S).

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal128OrDefault('0xc0fe', 1);.

Returned value

• Value of type Decimal(38, S) if successful, otherwise returns the default value if passed or 0 if not. Decimal128(S).

Examples

Query:

SELECT
    toDecimal128OrDefault(toString(1/42), 18) AS a,
    toTypeName(a),
    toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)')) AS b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Decimal(38, 18)
b:             -1
toTypeName(b): Decimal(38, 0)

toDecimal256¶

Converts an input value to a value of type Decimal(76, S) with scale of S. Throws an exception in case of an error.

Syntax

toDecimal256(expr, S)

Arguments

• expr: Expression returning a number or a string representation of a number. Expression.
• S: Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• Values or string representations of type (U)Int8/16/32/64/128/256.
• Values or string representations of type Float32/64.

Unsupported arguments:

• Values or string representations of Float32/64 values NaN and Inf (case-insensitive).
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal256('0xc0fe', 1);.

Returned value

• Value of type Decimal(76, S). Decimal256(S).

Example

Query:

SELECT
    toDecimal256(99, 1) AS a, toTypeName(a) AS type_a,
    toDecimal256(99.67, 2) AS b, toTypeName(b) AS type_b,
    toDecimal256('99.67', 3) AS c, toTypeName(c) AS type_c

Result:

Row 1:
──────
a:      99
type_a: Decimal(76, 1)
b:      99.67
type_b: Decimal(76, 2)
c:      99.67
type_c: Decimal(76, 3)

toDecimal256OrZero¶

Like toDecimal256, this function converts an input value to a value of type Decimal(76, S) but returns 0 in case of an error.

Syntax

toDecimal256OrZero(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal256OrZero('0xc0fe', 1);.

Returned value

• Value of type Decimal(76, S) if successful, otherwise 0 with S decimal places. Decimal256(S).

Example

Query:

SELECT
    toDecimal256OrZero(toString(0.0001), 76) AS a,
    toTypeName(a),
    toDecimal256OrZero(toString('Inf'), 76) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.0001
toTypeName(a): Decimal(76, 76)
b:             0
toTypeName(b): Decimal(76, 76)

toDecimal256OrNull¶

Like toDecimal256, this function converts an input value to a value of type Nullable(Decimal(76, S)) but returns 0 in case of an error.

Syntax

toDecimal256OrNull(expr, S)

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal256OrNull('0xc0fe', 1);.

Returned value

• Value of type Nullable(Decimal(76, S)) if successful, otherwise value NULL of the same type. Decimal256(S).

Examples

Query:

SELECT
    toDecimal256OrNull(toString(1/42), 76) AS a,
    toTypeName(a),
    toDecimal256OrNull(toString('Inf'), 76) as b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Nullable(Decimal(76, 76))
b:             ᴺᵁᴸᴸ
toTypeName(b): Nullable(Decimal(76, 76))

toDecimal256OrDefault¶

Like toDecimal256, this function converts an input value to a value of type Decimal(76, S) but returns the default value in case of an error.

Syntax

toDecimal256OrDefault(expr, S[, default])

Arguments

• expr: A String representation of a number. String.
• S: Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. UInt8.
• default (optional): The default value to return if parsing to type Decimal256(S) is unsuccessful. Decimal256(S).

Supported arguments:

• String representations of type (U)Int8/16/32/64/128/256.
• String representations of type Float32/64.

Unsupported arguments:

• String representations of Float32/64 values NaN and Inf.
• String representations of binary and hexadecimal values, e.g. SELECT toDecimal256OrDefault('0xc0fe', 1);.

Returned value

• Value of type Decimal(76, S) if successful, otherwise returns the default value if passed or 0 if not. Decimal256(S).

Examples

Query:

SELECT
    toDecimal256OrDefault(toString(1/42), 76) AS a,
    toTypeName(a),
    toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)')) AS b,
    toTypeName(b)

Result:

Row 1:
──────
a:             0.023809523809523808
toTypeName(a): Decimal(76, 76)
b:             -1
toTypeName(b): Decimal(76, 0)

toString¶

Functions for converting between numbers, strings (but not fixed strings), dates, and dates with times. All these functions accept one argument.

When converting to or from a string, the value is formatted or parsed using the same rules as for the TabSeparated format (and almost all other text formats). If the string can’t be parsed, an exception is thrown and the request is canceled.

When converting dates to numbers or vice versa, the date corresponds to the number of days since the beginning of the Unix epoch. When converting dates with times to numbers or vice versa, the date with time corresponds to the number of seconds since the beginning of the Unix epoch.

The date and date-with-time formats for the toDate/toDateTime functions are defined as follows:

YYYY-MM-DD
YYYY-MM-DD hh:mm:ss

As an exception, if converting from UInt32, Int32, UInt64, or Int64 numeric types to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date. This allows support for the common occurrence of writing toDate(unix_timestamp), which otherwise would be an error and would require writing the more cumbersome toDate(toDateTime(unix_timestamp)).

Conversion between a date and a date with time is performed the natural way: by adding a null time or dropping the time.

Conversion between numeric types uses the same rules as assignments between different numeric types in C++.

Additionally, the toString function of the DateTime argument can take a second String argument containing the name of the time zone. Example: Asia/Yekaterinburg In this case, the time is formatted according to the specified time zone.

Example

Query:

SELECT
    now() AS ts,
    time_zone,
    toString(ts, time_zone) AS str_tz_datetime
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10

Result:

┌──────────────────ts─┬─time_zone─────────┬─str_tz_datetime─────┐
│ 2023-09-08 19:14:59 │ Europe/Amsterdam  │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Andorra    │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Astrakhan  │ 2023-09-08 23:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Athens     │ 2023-09-08 22:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belfast    │ 2023-09-08 20:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Belgrade   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Berlin     │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bratislava │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Brussels   │ 2023-09-08 21:14:59 │
│ 2023-09-08 19:14:59 │ Europe/Bucharest  │ 2023-09-08 22:14:59 │
└─────────────────────┴───────────────────┴─────────────────────┘

Also see the toUnixTimestamp function.

toFixedString¶

Converts a String type argument to a FixedString(N) type (a string of fixed length N). If the string has fewer bytes than N, it is padded with null bytes to the right. If the string has more bytes than N, an exception is thrown.

Syntax

toFixedString(s, N)

Arguments

• s: A String to convert to a fixed string. String.
• N: Length N. UInt8

Returned value

• An N length fixed string of s. FixedString.

Example

Query:

SELECT toFixedString('foo', 8) AS s

Result:

┌─s─────────────┐
│ foo\0\0\0\0\0 │
└───────────────┘

toStringCutToZero¶

Accepts a String or FixedString argument. Returns the String with the content truncated at the first zero byte found.

Syntax

toStringCutToZero(s)

Example

Query:

SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut

Result:

┌─s─────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo   │
└───────────────┴───────┘

Query:

SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut

Result:

┌─s──────────┬─s_cut─┐
│ foo\0bar\0 │ foo   │
└────────────┴───────┘

toDecimalString¶

Converts a numeric value to String with the number of fractional digits in the output specified by the user.

Syntax

toDecimalString(number, scale)

Arguments

• number: Value to be represented as String, Int, UInt, Float, Decimal,
• scale: Number of fractional digits, UInt8.
  • Maximum scale for Decimal and Int, UInt types is 77 (it is the maximum possible number of significant digits for Decimal),
  • Maximum scale for Float is 60.

Returned value

• Input value represented as String with given number of fractional digits (scale). The number is rounded up or down according to common arithmetic in case requested scale is smaller than original number's scale.

Example

Query:

SELECT toDecimalString(CAST('64.32', 'Float64'), 5)

Result:

┌toDecimalString(CAST('64.32', 'Float64'), 5)─┐
│ 64.32000                                    │
└─────────────────────────────────────────────┘

reinterpretAsUInt8¶

Performs byte reinterpretation by treating the input value as a value of type UInt8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt8(x)

### Parameters

• x: value to byte reinterpret as UInt8. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt8. UInt8.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt8(x) AS res,
    toTypeName(res)

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ UInt8           │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt16¶

Performs byte reinterpretation by treating the input value as a value of type UInt16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt16(x)

### Parameters

• x: value to byte reinterpret as UInt16. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt16. UInt16.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsUInt16(x) AS res,
    toTypeName(res)

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ UInt16          │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt32¶

Performs byte reinterpretation by treating the input value as a value of type UInt32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt32(x)

### Parameters

• x: value to byte reinterpret as UInt32. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt32. UInt32.

Example

Query:

SELECT
    toUInt16(257) AS x,
    toTypeName(x),
    reinterpretAsUInt32(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt16        │ 257 │ UInt32          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt64¶

Performs byte reinterpretation by treating the input value as a value of type UInt64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt64(x)

### Parameters

• x: value to byte reinterpret as UInt64. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt64. UInt64.

Example

Query:

SELECT
    toUInt32(257) AS x,
    toTypeName(x),
    reinterpretAsUInt64(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt32        │ 257 │ UInt64          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt128¶

Performs byte reinterpretation by treating the input value as a value of type UInt128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt128(x)

### Parameters

• x: value to byte reinterpret as UInt128. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt128. UInt128.

Example

Query:

SELECT
    toUInt64(257) AS x,
    toTypeName(x),
    reinterpretAsUInt128(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt64        │ 257 │ UInt128         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsUInt256¶

Performs byte reinterpretation by treating the input value as a value of type UInt256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsUInt256(x)

### Parameters

• x: value to byte reinterpret as UInt256. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as UInt256. UInt256.

Example

Query:

SELECT
    toUInt128(257) AS x,
    toTypeName(x),
    reinterpretAsUInt256(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ UInt128       │ 257 │ UInt256         │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt8¶

Performs byte reinterpretation by treating the input value as a value of type Int8. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt8(x)

### Parameters

• x: value to byte reinterpret as Int8. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int8. Int8.

Example

Query:

SELECT
    toUInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt8(x) AS res,
    toTypeName(res)

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ UInt8         │   1 │ Int8            │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsInt16¶

Performs byte reinterpretation by treating the input value as a value of type Int16. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt16(x)

### Parameters

• x: value to byte reinterpret as Int16. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int16. Int16.

Example

Query:

SELECT
    toInt8(257) AS x,
    toTypeName(x),
    reinterpretAsInt16(x) AS res,
    toTypeName(res)

Result:

┌─x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 1 │ Int8          │   1 │ Int16           │
└───┴───────────────┴─────┴─────────────────┘

reinterpretAsInt32¶

Performs byte reinterpretation by treating the input value as a value of type Int32. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt32(x)

### Parameters

• x: value to byte reinterpret as Int32. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int32. Int32.

Example

Query:

SELECT
    toInt16(257) AS x,
    toTypeName(x),
    reinterpretAsInt32(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int16         │ 257 │ Int32           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt64¶

Performs byte reinterpretation by treating the input value as a value of type Int64. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt64(x)

### Parameters

• x: value to byte reinterpret as Int64. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int64. Int64.

Example

Query:

SELECT
    toInt32(257) AS x,
    toTypeName(x),
    reinterpretAsInt64(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int32         │ 257 │ Int64           │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt128¶

Performs byte reinterpretation by treating the input value as a value of type Int128. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt128(x)

### Parameters

• x: value to byte reinterpret as Int128. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int128. Int128.

Example

Query:

SELECT
    toInt64(257) AS x,
    toTypeName(x),
    reinterpretAsInt128(x) AS res,
    toTypeName(res)

Result:

┌───x─┬─toTypeName(x)─┬─res─┬─toTypeName(res)─┐
│ 257 │ Int64         │ 257 │ Int128          │
└─────┴───────────────┴─────┴─────────────────┘

reinterpretAsInt256¶

Performs byte reinterpretation by treating the input value as a value of type Int256. Unlike CAST, the function does not attempt to preserve the original value - if the target type is not able to represent the input type, the output is meaningless.

Syntax

reinterpretAsInt256(x)

### Parameters

• x: value to byte reinterpret as Int256. (U)Int*, Float, Date, DateTime, UUID, String or FixedString.

Returned value

• Reinterpreted value x as Int256. Int256.

Example

Query:

SELECT
    toInt128(257) AS x,
    toTypeName(x),
    reinterpretAsInt256(x) AS res,
    toTypeName(res)

Result: