Type conversion functions¶

The following functions are available for type conversion.

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 isn't 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 boolean type. This function will throw an error if the conversion is not possible.

Syntax¶

toBool(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string values: 'true' or 'false' (case-insensitive).

Returns¶

A boolean value (true or false). Bool.

Example¶

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 an 8-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt8(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

An 8-bit signed integer. Int8.

Example¶

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

Result:

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

toInt8OrZero¶

Converts an input value to an 8-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt8OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

An 8-bit signed integer if successful, otherwise 0. Int8.

Example¶

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

Result:

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

toInt8OrNull¶

Converts an input value to an 8-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt8OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

An 8-bit signed integer if successful, otherwise NULL. Int8 / Nullable.

Example¶

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

Result:

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

toInt8OrDefault¶

Converts an input value to an 8-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt8OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int8 value to return if the conversion of expr is unsuccessful.

Returns¶

An 8-bit signed integer if successful, otherwise the default value (if provided) or 0. Int8.

Example¶

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 16-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt16(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 16-bit signed integer. Int16.

Example¶

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

Result:

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

toInt16OrZero¶

Converts an input value to a 16-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt16OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 16-bit signed integer if successful, otherwise 0. Int16.

Example¶

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

Result:

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

toInt16OrNull¶

Converts an input value to a 16-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt16OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 16-bit signed integer if successful, otherwise NULL. Int16 / Nullable.

Example¶

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

Result:

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

toInt16OrDefault¶

Converts an input value to a 16-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt16OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int16 value to return if the conversion of expr is unsuccessful.

Returns¶

A 16-bit signed integer if successful, otherwise the default value (if provided) or 0. Int16.

Example¶

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 32-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt32(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 32-bit signed integer. Int32.

Example¶

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

Result:

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

toInt32OrZero¶

Converts an input value to a 32-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt32OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 32-bit signed integer if successful, otherwise 0. Int32.

Example¶

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

Result:

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

toInt32OrNull¶

Converts an input value to a 32-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt32OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 32-bit signed integer if successful, otherwise NULL. Int32 / Nullable.

Example¶

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

Result:

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

toInt32OrDefault¶

Converts an input value to a 32-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt32OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int32 value to return if the conversion of expr is unsuccessful.

Returns¶

A 32-bit signed integer if successful, otherwise the default value (if provided) or 0. Int32.

Example¶

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 64-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt64(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 64-bit signed integer. Int64.

Example¶

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

Result:

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

toInt64OrZero¶

Converts an input value to a 64-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt64OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 64-bit signed integer if successful, otherwise 0. Int64.

Example¶

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

Result:

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

toInt64OrNull¶

Converts an input value to a 64-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt64OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 64-bit signed integer if successful, otherwise NULL. Int64 / Nullable.

Example¶

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

Result:

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

toInt64OrDefault¶

Converts an input value to a 64-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt64OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int64 value to return if the conversion of expr is unsuccessful.

Returns¶

A 64-bit signed integer if successful, otherwise the default value (if provided) or 0. Int64.

Example¶

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 128-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt128(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 128-bit signed integer. Int128.

Example¶

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

Result:

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

toInt128OrZero¶

Converts an input value to a 128-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt128OrZero(expr)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 128-bit signed integer if successful, otherwise 0. Int128.

Example¶

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

Result:

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

toInt128OrNull¶

Converts an input value to a 128-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt128OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 128-bit signed integer if successful, otherwise NULL. Int128 / Nullable.

Example¶

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

Result:

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

toInt128OrDefault¶

Converts an input value to a 128-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt128OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int128 value to return if the conversion of expr is unsuccessful.

Returns¶

A 128-bit signed integer if successful, otherwise the default value (if provided) or 0. Int128.

Example¶

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 256-bit signed integer. This function will throw an error if the conversion is not possible.

Syntax¶

toInt256(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 256-bit signed integer. Int256.

Example¶

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

Result:

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

toInt256OrZero¶

Converts an input value to a 256-bit signed integer, returning 0 if the conversion fails.

Syntax¶

toInt256OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 256-bit signed integer if successful, otherwise 0. Int256.

Example¶

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

Result:

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

toInt256OrNull¶

Converts an input value to a 256-bit signed integer, returning NULL if the conversion fails.

Syntax¶

toInt256OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 256-bit signed integer if successful, otherwise NULL. Int256 / Nullable.

Example¶

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

Result:

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

toInt256OrDefault¶

Converts an input value to a 256-bit signed integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toInt256OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The Int256 value to return if the conversion of expr is unsuccessful.

Returns¶

A 256-bit signed integer if successful, otherwise the default value (if provided) or 0. Int256.

Example¶

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 an 8-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt8(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

An 8-bit unsigned integer. UInt8.

Example¶

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

Result:

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

toUInt8OrZero¶

Converts an input value to an 8-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt8OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt8OrNull¶

Converts an input value to an 8-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt8OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

An 8-bit unsigned integer if successful, otherwise NULL. UInt8 / Nullable.

Example¶

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

Result:

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

toUInt8OrDefault¶

Converts an input value to an 8-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt8OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt8 value to return if the conversion of expr is unsuccessful.

Returns¶

An 8-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt8.

Example¶

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 16-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt16(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 16-bit unsigned integer. UInt16.

Example¶

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

Result:

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

toUInt16OrZero¶

Converts an input value to a 16-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt16OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt16OrNull¶

Converts an input value to a 16-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt16OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 16-bit unsigned integer if successful, otherwise NULL. UInt16 / Nullable.

Example¶

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

Result:

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

toUInt16OrDefault¶

Converts an input value to a 16-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt16OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt16 value to return if the conversion of expr is unsuccessful.

Returns¶

A 16-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt16.

Example¶

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 32-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt32(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 32-bit unsigned integer. UInt32.

Example¶

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

Result:

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

toUInt32OrZero¶

Converts an input value to a 32-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt32OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt32OrNull¶

Converts an input value to a 32-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt32OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 32-bit unsigned integer if successful, otherwise NULL. UInt32 / Nullable.

Example¶

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

Result:

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

toUInt32OrDefault¶

Converts an input value to a 32-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt32OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt32 value to return if the conversion of expr is unsuccessful.

Returns¶

A 32-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt32.

Example¶

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 64-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt64(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 64-bit unsigned integer. UInt64.

Example¶

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

Result:

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

toUInt64OrZero¶

Converts an input value to a 64-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt64OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt64OrNull¶

Converts an input value to a 64-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt64OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 64-bit unsigned integer if successful, otherwise NULL. UInt64 / Nullable.

Example¶

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

Result:

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

toUInt64OrDefault¶

Converts an input value to a 64-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt64OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt64 value to return if the conversion of expr is unsuccessful.

Returns¶

A 64-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt64.

Example¶

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 128-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt128(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 128-bit unsigned integer. UInt128.

Example¶

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

Result:

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

toUInt128OrZero¶

Converts an input value to a 128-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt128OrZero(expr)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt128OrNull¶

Converts an input value to a 128-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt128OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 128-bit unsigned integer if successful, otherwise NULL. UInt128 / Nullable.

Example¶

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

Result:

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

toUInt128OrDefault¶

Converts an input value to a 128-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt128OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt128 value to return if the conversion of expr is unsuccessful.

Returns¶

A 128-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt128.

Example¶

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 256-bit unsigned integer. This function will throw an error if the conversion is not possible.

Syntax¶

toUInt256(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations: Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 256-bit unsigned integer. UInt256.

Example¶

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

Result:

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

toUInt256OrZero¶

Converts an input value to a 256-bit unsigned integer, returning 0 if the conversion fails.

Syntax¶

toUInt256OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

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

Example¶

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

Result:

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

toUInt256OrNull¶

Converts an input value to a 256-bit unsigned integer, returning NULL if the conversion fails.

Syntax¶

toUInt256OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers.
  • Unsupported string representations (return NULL): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.

Returns¶

A 256-bit unsigned integer if successful, otherwise NULL. UInt256 / Nullable.

Example¶

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

Result:

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

toUInt256OrDefault¶

Converts an input value to a 256-bit unsigned integer, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toUInt256OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers.
  • Unsupported string representations (return default or 0): Floating-point numbers (e.g., '1.23'), NaN, Inf, binary, or hexadecimal values.
• default (optional): The UInt256 value to return if the conversion of expr is unsuccessful.

Returns¶

A 256-bit unsigned integer if successful, otherwise the default value (if provided) or 0. UInt256.

Example¶

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 32-bit floating-point number. This function will throw an error if the conversion is not possible.

Syntax¶

toFloat32(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf (case-insensitive).
  • Unsupported string representations: Binary or hexadecimal values.

Returns¶

A 32-bit floating-point number. Float32.

Example¶

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¶

Converts an input value to a 32-bit floating-point number, returning 0 if the conversion fails.

Syntax¶

toFloat32OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf.
  • Unsupported string representations (return 0): Binary or hexadecimal values.

Returns¶

A 32-bit floating-point number if successful, otherwise 0. Float32.

Example¶

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

Result:

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

toFloat32OrNull¶

Converts an input value to a 32-bit floating-point number, returning NULL if the conversion fails.

Syntax¶

toFloat32OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf.
  • Unsupported string representations (return NULL): Binary or hexadecimal values.

Returns¶

A 32-bit floating-point number if successful, otherwise NULL. Float32 / Nullable.

Example¶

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

Result:

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

toFloat32OrDefault¶

Converts an input value to a 32-bit floating-point number, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toFloat32OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf (case-insensitive).
  • Unsupported string representations (return default or 0): Binary or hexadecimal values.
• default (optional): The Float32 value to return if the conversion of expr is unsuccessful.

Returns¶

A 32-bit floating-point number if successful, otherwise the default value (if provided) or 0. Float32.

Example¶

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 64-bit floating-point number. This function will throw an error if the conversion is not possible.

Syntax¶

toFloat64(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf (case-insensitive).
  • Unsupported string representations: Binary or hexadecimal values.

Returns¶

A 64-bit floating-point number. Float64.

Example¶

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¶

Converts an input value to a 64-bit floating-point number, returning 0 if the conversion fails.

Syntax¶

toFloat64OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf.
  • Unsupported string representations (return 0): Binary or hexadecimal values.

Returns¶

A 64-bit floating-point number if successful, otherwise 0. Float64.

Example¶

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

Result:

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

toFloat64OrNull¶

Converts an input value to a 64-bit floating-point number, returning NULL if the conversion fails.

Syntax¶

toFloat64OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf.
  • Unsupported string representations (return NULL): Binary or hexadecimal values.

Returns¶

A 64-bit floating-point number if successful, otherwise NULL. Float64 / Nullable.

Example¶

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

Result:

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

toFloat64OrDefault¶

Converts an input value to a 64-bit floating-point number, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toFloat64OrDefault(expr[, default])

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf (case-insensitive).
  • Unsupported string representations (return default or 0): Binary or hexadecimal values.
• default (optional): The Float64 value to return if the conversion of expr is unsuccessful.

Returns¶

A 64-bit floating-point number if successful, otherwise the default value (if provided) or 0. Float64.

Example¶

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

toBFloat16¶

Converts an input value to a 16-bit brain floating-point number. This function will throw an error if the conversion is not possible.

Syntax¶

toBFloat16(expr)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers, NaN, Inf (case-insensitive).

Returns¶

A 16-bit brain floating-point value. BFloat16.

Example¶

SELECT toBFloat16(toFloat32(42.7))

Result:

┌─toBFloat16(toFloat32(42.7))─┐
│                       42.5 │
└────────────────────────────┘

toBFloat16OrZero¶

Converts a string input value to a 16-bit brain floating-point number, returning 0 if the conversion fails.

Syntax¶

toBFloat16OrZero(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Numeric values (integers, floating-point numbers, NaN, Inf).
  • Unsupported string representations (return 0): Binary or hexadecimal values.

Returns¶

A 16-bit brain floating-point value if successful, otherwise 0. BFloat16.

Example¶

SELECT toBFloat16OrZero('0x5E'), toBFloat16OrZero('12.3'), toBFloat16OrZero('12.3456789')

Result:

┌─toBFloat16OrZero('0x5E')─┬─toBFloat16OrZero('12.3')─┬─toBFloat16OrZero('12.3456789')─┐
│                        0 │                    12.25 │                         12.3125 │
└──────────────────────────┴──────────────────────────┴────────────────────────────────┘

toBFloat16OrNull¶

Converts a string input value to a 16-bit brain floating-point number, returning NULL if the conversion fails.

Syntax¶

toBFloat16OrNull(x)

Arguments¶

• x: A string representation of a number.
  • Supported string representations: Numeric values (integers, floating-point numbers, NaN, Inf).
  • Unsupported string representations (return NULL): Binary or hexadecimal values.

Returns¶

A 16-bit brain floating-point value if successful, otherwise NULL. BFloat16 / Nullable.

Example¶

SELECT toBFloat16OrNull('0x5E'), toBFloat16OrNull('12.3'), toBFloat16OrNull('12.3456789')

Result:

┌─toBFloat16OrNull('0x5E')─┬─toBFloat16OrNull('12.3')─┬─toBFloat16OrNull('12.3456789')─┐
│                     ᴺᵁᴸᴸ │                    12.25 │                         12.3125 │
└──────────────────────────┴──────────────────────────┴────────────────────────────────┘

toDate¶

Converts an input value to a Date data type.

Syntax¶

toDate(expr[, time_zone])

Arguments¶

• expr: The value to convert. Can be a String, Int, Date, or DateTime / DateTime64.
  • If expr is a DateTime or DateTime64, it extracts only the date component.
  • If expr is a String, it parses the string as a date or datetime and extracts the date component.
  • If expr is a number greater than or equal to 65536, it's interpreted as a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) and converted to a date.
  • If expr is a number less than 65536, it's interpreted as the number of days since 1970-01-01.
• time_zone (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. String.

Returns¶

A date value. Date.

Example¶

SELECT
    toDate('2022-12-30 01:02:03') AS date_from_datetime_string,
    toDate(1672404714, 'Pacific/Apia') AS date_from_unix_timestamp_with_timezone,
    toDate(12345) AS date_from_days_since_epoch

Result:

┌─date_from_datetime_string─┬─date_from_unix_timestamp_with_timezone─┬─date_from_days_since_epoch─┐
│                2022-12-30 │                             2022-12-31 │                 2003-10-20 │
└───────────────────────────┴────────────────────────────────────────┴────────────────────────────┘

toDateOrZero¶

Converts a string to a Date data type, returning the minimum Date value (1970-01-01) if the conversion fails.

Syntax¶

toDateOrZero(str)

Arguments¶

• str: The string to convert to a date. String.

Returns¶

A date value if successful, otherwise 1970-01-01. Date.

Example¶

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

Result:

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

toDateOrNull¶

Converts a string to a Date data type, returning NULL if the conversion fails.

Syntax¶

toDateOrNull(str)

Arguments¶

• str: The string to convert to a date. String.

Returns¶

A date value if successful, otherwise NULL. Date / Nullable.

Example¶

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

Result:

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

toDateOrDefault¶

Converts an input value to a Date data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum Date value (1970-01-01).

Syntax¶

toDateOrDefault(expr[, default_value])

Arguments¶

• expr: The value to convert to a date. Can be a String, Int, Date, or DateTime / DateTime64.
• default_value (optional): The Date value to return if the conversion of expr is unsuccessful.

Returns¶

A date value if successful, otherwise the default_value (if provided) or 1970-01-01. Date.

Example¶

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

Result:

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

toDateTime¶

Converts an input value to a DateTime data type.

Syntax¶

toDateTime(expr[, time_zone])

Arguments¶

• expr: The value to convert. Can be a String, Int, Date, or DateTime.
  • If expr is a number, it's interpreted as a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC).
  • If expr is a string, it's parsed as a datetime string. Parsing of short numeric strings (up to 4 digits) is disabled to avoid ambiguity with Unix timestamps.
• time_zone (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. String.

Returns¶

A datetime value. DateTime.

Example¶

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¶

Converts a string to a DateTime data type, returning the minimum DateTime value (1970-01-01 00:00:00) if the conversion fails.

Syntax¶

toDateTimeOrZero(str)

Arguments¶

• str: The string to convert to a datetime. String.

Returns¶

A datetime value if successful, otherwise 1970-01-01 00:00:00. DateTime.

Example¶

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

Result:

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

toDateTimeOrNull¶

Converts a string to a DateTime data type, returning NULL if the conversion fails.

Syntax¶

toDateTimeOrNull(str)

Arguments¶

• str: The string to convert to a datetime. String.

Returns¶

A datetime value if successful, otherwise NULL. DateTime / Nullable.

Example¶

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

Result:

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

toDateTimeOrDefault¶

Converts an input value to a DateTime data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum DateTime value (1970-01-01 00:00:00).

Syntax¶

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

Arguments¶

• expr: The value to convert. Can be a String, Int, Date, or DateTime.
• time_zone (optional): A string specifying the timezone for interpreting Unix timestamps or datetime strings. String.
• default_value (optional): The DateTime value to return if the conversion of expr is unsuccessful.

Returns¶

A datetime value if successful, otherwise the default_value (if provided) or 1970-01-01 00:00:00. DateTime.

Example¶

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

Result:

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

toDate32¶

Converts an input value to a Date32 data type. This function will throw an error if the conversion is not possible.

Syntax¶

toDate32(expr)

Arguments¶

• expr: The value to convert. Can be a String, UInt32, or Date.
  • If expr is a Date, its range is considered.
  • If expr is a String, it parses the string as a date.

Returns¶

A calendar date. Date32.

Example¶

SELECT
    toDate32('1955-01-01') AS valid_date,
    toDate32('1899-01-01') AS out_of_range_date,
    toDate32(toDate('1899-01-01')) AS date_from_date_type_out_of_range

Result:

┌─valid_date─┬─out_of_range_date─┬─date_from_date_type_out_of_range─┐
│ 1955-01-01 │        1900-01-01 │                         1970-01-01 │
└────────────┴───────────────────┴──────────────────────────────────┘

toDate32OrZero¶

Converts an input value to a Date32 data type, returning the minimum Date32 value (1900-01-01) if the conversion fails.

Syntax¶

toDate32OrZero(expr)

Arguments¶

• expr: The value to convert. Can be a String, UInt32, or Date.

Returns¶

A calendar date if successful, otherwise 1900-01-01. Date32.

Example¶

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

Result:

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

toDate32OrNull¶

Converts an input value to a Date32 data type, returning NULL if the conversion fails.

Syntax¶

toDate32OrNull(expr)

Arguments¶

• expr: The value to convert. Can be a String, UInt32, or Date.

Returns¶

A calendar date if successful, otherwise NULL. Date32 / Nullable.

Example¶

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

Result:

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

toDate32OrDefault¶

Converts an input value to a Date32 data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum Date32 value (1900-01-01).

Syntax¶

toDate32OrDefault(expr[, default_value])

Arguments¶

• expr: The value to convert. Can be a String, UInt32, or Date.
• default_value (optional): The Date32 value to return if the conversion of expr is unsuccessful.

Returns¶

A calendar date if successful, otherwise the default_value (if provided) or 1900-01-01. Date32.

Example¶

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 DateTime64 data type with specified precision. This function will throw an error if the conversion is not possible.

Syntax¶

toDateTime64(expr, scale, [timezone])

Arguments¶

• expr: The value to convert. Can be a String, UInt32, Float, or DateTime.
• scale: An integer from 0 to 9 representing the number of decimal places for sub-second precision. UInt8.
• timezone (optional): A string specifying the timezone for interpreting the datetime. String.

Returns¶

A calendar date and time with sub-second precision. DateTime64.

Example¶

SELECT
    toDateTime64('1955-01-01 00:00:00.000', 3) AS datetime_from_string,
    toDateTime64(1546300800.000, 3) AS datetime_from_float_unix_timestamp,
    toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul') AS datetime_with_timezone

Result:

┌─datetime_from_string─┬─datetime_from_float_unix_timestamp─┬─datetime_with_timezone─┐
│ 1955-01-01 00:00:00.000 │          2019-01-01 00:00:00.000 │ 2019-01-01 00:00:00.000 │
└───────────────────────┴──────────────────────────────────┴────────────────────────┘

toDateTime64OrZero¶

Converts an input value to a DateTime64 data type, returning the minimum DateTime64 value (1970-01-01 01:00:00.000) if the conversion fails.

Syntax¶

toDateTime64OrZero(expr, scale, [timezone])

Arguments¶

• expr: The value to convert. Can be a String, UInt32, Float, or DateTime.
• scale: An integer from 0 to 9 representing the number of decimal places for sub-second precision. UInt8.
• timezone (optional): A string specifying the timezone for interpreting the datetime. String.

Returns¶

A calendar date and time with sub-second precision if successful, otherwise 1970-01-01 01:00:00.000. DateTime64.

Example¶

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¶

Converts an input value to a DateTime64 data type, returning NULL if the conversion fails.

Syntax¶

toDateTime64OrNull(expr, scale, [timezone])

Arguments¶

• expr: The value to convert. Can be a String, UInt32, Float, or DateTime.
• scale: An integer from 0 to 9 representing the number of decimal places for sub-second precision. UInt8.
• timezone (optional): A string specifying the timezone for interpreting the datetime. String.

Returns¶

A calendar date and time with sub-second precision if successful, otherwise NULL. DateTime64 / Nullable.

Example¶

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¶

Converts an input value to a DateTime64 data type, returning a specified default value if the conversion fails. If no default is provided, it returns the minimum DateTime64 value (1970-01-01 01:00:00.000).

Syntax¶

toDateTime64OrDefault(expr, scale, [timezone, default_value])

Arguments¶

• expr: The value to convert. Can be a String, UInt32, Float, or DateTime.
• scale: An integer from 0 to 9 representing the number of decimal places for sub-second precision. UInt8.
• timezone (optional): A string specifying the timezone for interpreting the datetime. String.
• default_value (optional): The DateTime64 value to return if the conversion of expr is unsuccessful.

Returns¶

A calendar date and time with sub-second precision if successful, otherwise the default_value (if provided) or 1970-01-01 01:00:00.000. DateTime64.

Example¶

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 Decimal32 data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows.

Syntax¶

toDecimal32(expr, S)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations: NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 9 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal32 value with the specified scale. Decimal32(S).

Example¶

SELECT
    toDecimal32(2, 1) AS a,
    toDecimal32(4.2, 2) AS b,
    toDecimal32('4.2', 3) AS c

Result:

┌─a─┬─b───┬─c───┐
│ 2 │ 4.2 │ 4.2 │
└───┴─────┴─────┘

toDecimal32OrZero¶

Converts an input value to a Decimal32 data type with a specified scale, returning 0 if the conversion fails.

Syntax¶

toDecimal32OrZero(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 9 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal32 value if successful, otherwise 0 with S decimal places. Decimal32(S).

Example¶

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

Result:

┌───────a─┬─b─┐
│ -1.11100 │ 0 │
└─────────┴───┘

toDecimal32OrNull¶

Converts an input value to a Decimal32 data type with a specified scale, returning NULL if the conversion fails.

Syntax¶

toDecimal32OrNull(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return NULL): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 9 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal32 value if successful, otherwise NULL. Nullable(Decimal32(S)).

Example¶

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

Result:

┌───────a─┬─b─┐
│ -1.11100 │ ᴺᵁᴸᴸ │
└─────────┴───┘

toDecimal32OrDefault¶

Converts an input value to a Decimal32 data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toDecimal32OrDefault(expr, S[, default])

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return default or 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 9 representing the number of decimal places for the fractional part. UInt8.
• default (optional): The Decimal32(S) value to return if the conversion of expr is unsuccessful.

Returns¶

A Decimal32 value if successful, otherwise the default value (if provided) or 0. Decimal32(S).

Example¶

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

Result:

┌────────a─┬─b─┐
│ 0.00010 │ -1 │
└─────────┴───┘

toDecimal64¶

Converts an input value to a Decimal64 data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows.

Syntax¶

toDecimal64(expr, S)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations: NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 18 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal64 value with the specified scale. Decimal64(S).

Example¶

SELECT
    toDecimal64(2, 1) AS a,
    toDecimal64(4.2, 2) AS b,
    toDecimal64('4.2', 3) AS c

Result:

┌─a─┬─b───┬─c───┐
│ 2 │ 4.2 │ 4.2 │
└───┴─────┴─────┘

toDecimal64OrZero¶

Converts an input value to a Decimal64 data type with a specified scale, returning 0 if the conversion fails.

Syntax¶

toDecimal64OrZero(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 18 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal64 value if successful, otherwise 0 with S decimal places. Decimal64(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.000100000000000000 │ 0 │
└────────────┴───┘

toDecimal64OrNull¶

Converts an input value to a Decimal64 data type with a specified scale, returning NULL if the conversion fails.

Syntax¶

toDecimal64OrNull(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return NULL): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 18 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal64 value if successful, otherwise NULL. Nullable(Decimal64(S)).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.000100000000000000 │ ᴺᵁᴸᴸ │
└────────────┴───┘

toDecimal64OrDefault¶

Converts an input value to a Decimal64 data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toDecimal64OrDefault(expr, S[, default])

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return default or 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 18 representing the number of decimal places for the fractional part. UInt8.
• default (optional): The Decimal64(S) value to return if the conversion of expr is unsuccessful.

Returns¶

A Decimal64 value if successful, otherwise the default value (if provided) or 0. Decimal64(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.000100000000000000 │ -1 │
└────────────┴───┘

toDecimal128¶

Converts an input value to a Decimal128 data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows.

Syntax¶

toDecimal128(expr, S)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations: NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 38 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal128 value with the specified scale. Decimal128(S).

Example¶

SELECT
    toDecimal128(99, 1) AS a,
    toDecimal128(99.67, 2) AS b,
    toDecimal128('99.67', 3) AS c

Result:

┌─a─┬─b─────┬─c─────┐
│ 99 │ 99.67 │ 99.67 │
└───┴───────┴───────┘

toDecimal128OrZero¶

Converts an input value to a Decimal128 data type with a specified scale, returning 0 if the conversion fails.

Syntax¶

toDecimal128OrZero(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 38 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal128 value if successful, otherwise 0 with S decimal places. Decimal128(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.0001000000000000000000000000000000000000 │ 0 │
└────────────┴───┘

toDecimal128OrNull¶

Converts an input value to a Decimal128 data type with a specified scale, returning NULL if the conversion fails.

Syntax¶

toDecimal128OrNull(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return NULL): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 38 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal128 value if successful, otherwise NULL. Nullable(Decimal128(S)).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.02380952380952380800000000000000000000 │ ᴺᵁᴸᴸ │
└────────────┴───┘

toDecimal128OrDefault¶

Converts an input value to a Decimal128 data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toDecimal128OrDefault(expr, S[, default])

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return default or 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 38 representing the number of decimal places for the fractional part. UInt8.
• default (optional): The Decimal128(S) value to return if the conversion of expr is unsuccessful.

Returns¶

A Decimal128 value if successful, otherwise the default value (if provided) or 0. Decimal128(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.023809523809523808 │ -1 │
└────────────┴───┘

toDecimal256¶

Converts an input value to a Decimal256 data type with a specified scale. This function will throw an error if the conversion is not possible or if the integer part overflows.

Syntax¶

toDecimal256(expr, S)

Arguments¶

• expr: An expression that evaluates to a number or a string representation of a number.
  • Supported numeric types: Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256, Float32, Float64.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations: NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 76 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal256 value with the specified scale. Decimal256(S).

Example¶

SELECT
    toDecimal256(99, 1) AS a,
    toDecimal256(99.67, 2) AS b,
    toDecimal256('99.67', 3) AS c

Result:

┌─a─┬─b─────┬─c─────┐
│ 99 │ 99.67 │ 99.67 │
└───┴───────┴───────┘

toDecimal256OrZero¶

Converts an input value to a Decimal256 data type with a specified scale, returning 0 if the conversion fails.

Syntax¶

toDecimal256OrZero(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 76 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal256 value if successful, otherwise 0 with S decimal places. Decimal256(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.0001000000000000000000000000000000000000000000000000000000000000000000000000 │ 0 │
└────────────┴───┘

toDecimal256OrNull¶

Converts an input value to a Decimal256 data type with a specified scale, returning NULL if the conversion fails.

Syntax¶

toDecimal256OrNull(expr, S)

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return NULL): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 76 representing the number of decimal places for the fractional part. UInt8.

Returns¶

A Decimal256 value if successful, otherwise NULL. Nullable(Decimal256(S)).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.02380952380952380800000000000000000000000000000000000000000000000000000000 │ ᴺᵁᴸᴸ │
└────────────┴───┘

toDecimal256OrDefault¶

Converts an input value to a Decimal256 data type with a specified scale, returning a specified default value if the conversion fails. If no default is provided, it returns 0.

Syntax¶

toDecimal256OrDefault(expr, S[, default])

Arguments¶

• expr: A string representation of a number.
  • Supported string representations: Integers, floating-point numbers.
  • Unsupported string representations (return default or 0): NaN, Inf, binary, or hexadecimal values.
• S: An integer from 0 to 76 representing the number of decimal places for the fractional part. UInt8.
• default (optional): The Decimal256(S) value to return if the conversion of expr is unsuccessful.

Returns¶

A Decimal256 value if successful, otherwise the default value (if provided) or 0. Decimal256(S).

Example¶

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

Result:

┌──────────a─┬─b─┐
│ 0.02380952380952380800000000000000000000000000000000000000000000000000000000 │ -1 │
└────────────┴───┘

toString¶

Converts a value of any supported data type to its string representation.

Syntax¶

toString(expr[, time_zone])

Arguments¶

• expr: The value to convert. Can be a number, string, date, or datetime.
• time_zone (optional): For DateTime arguments, a string specifying the timezone for formatting the output. String.

Returns¶

A string representation of the input value. String.

Example¶

SELECT
    toString(123) AS num_to_string,
    toString(toDate('2023-01-15')) AS date_to_string,
    toString(toDateTime('2023-01-15 10:30:00'), 'Europe/Berlin') AS datetime_to_string_with_tz

Result:

┌─num_to_string─┬─date_to_string─┬─datetime_to_string_with_tz─┐
│ 123           │ 2023-01-15     │ 2023-01-15 11:30:00        │
└───────────────┴────────────────┴────────────────────────────┘

toFixedString¶

Converts a string to a FixedString(N) data type, padding with null bytes or throwing an error if the length is mismatched.

Syntax¶

toFixedString(s, N)

Arguments¶

• s: The string to convert. String.
• N: The desired fixed length of the string. UInt8.

Returns¶

A fixed-length string. FixedString.

Example¶

SELECT toFixedString('foo', 8) AS s

Result:

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

toStringCutToZero¶

Truncates a string at the first null byte encountered.

Syntax¶

toStringCutToZero(s)

Arguments¶

• s: The string to process. Can be a String or FixedString.

Returns¶

A string truncated at the first null byte. String.

Example¶

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 its string representation with a specified number of fractional digits.

Syntax¶

toDecimalString(number, scale)

Arguments¶

• number: The numeric value to convert. Can be an Int, UInt, Float, or Decimal.
• scale: The number of fractional digits to include in the output string. UInt8.
  • Maximum scale for Decimal and integer types is 77.
  • Maximum scale for Float types is 60.

Returns¶

A string representation of the number with the specified fractional digits. String.

Example¶

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

Result:

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

reinterpretAsUInt8¶

Performs a byte-level reinterpretation of an input value as an 8-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt8(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as an 8-bit unsigned integer. UInt8.

Example¶

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

Result:

┌─x─┬─res─┐
│ 1 │   1 │
└───┴─────┘

reinterpretAsUInt16¶

Performs a byte-level reinterpretation of an input value as a 16-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt16(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as a 16-bit unsigned integer. UInt16.

Example¶

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

Result:

┌─x─┬─res─┐
│ 1 │   1 │
└───┴─────┘

reinterpretAsUInt32¶

Performs a byte-level reinterpretation of an input value as a 32-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt32(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as a 32-bit unsigned integer. UInt32.

Example¶

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

Result:

┌───x─┬─res─┐
│ 257 │ 257 │
└─────┴─────┘

reinterpretAsUInt64¶

Performs a byte-level reinterpretation of an input value as a 64-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt64(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as a 64-bit unsigned integer. UInt64.

Example¶

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

Result:

┌───x─┬─res─┐
│ 257 │ 257 │
└─────┴─────┘

reinterpretAsUInt128¶

Performs a byte-level reinterpretation of an input value as a 128-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt128(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as a 128-bit unsigned integer. UInt128.

Example¶

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

Result:

┌───x─┬─res─┐
│ 257 │ 257 │
└─────┴─────┘

reinterpretAsUInt256¶

Performs a byte-level reinterpretation of an input value as a 256-bit unsigned integer. This function does not attempt to preserve the original value's meaning.

Syntax¶

reinterpretAsUInt256(x)

Arguments¶

• x: The value to reinterpret. Can be any numeric type, Date, DateTime, UUID, String, or FixedString.

Returns¶

The byte-reinterpreted value as a 256-bit unsigned integer. UInt256.

Example¶

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

Result: