Type conversion functions

Supported arguments:

- Values or string representations of type (U)Int\*.
- Values or string representations of type Float\*.

Unsupported arguments (return `NULL`):

- Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
- String representations of binary and hexadecimal values.
- Values that exceed the bounds of `Decimal256`: `(-1 * 10^(76 - S), 1 * 10^(76 - S))`.

See also:

- [`toDecimal256`](#toDecimal256).
- [`toDecimal256OrZero`](#toDecimal256OrZero).
- [`toDecimal256OrDefault`](#toDecimal256OrDefault).

**Syntax**

```
toDecimal256OrNull(expr, S)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Decimal(76, S) value if successful, otherwise `NULL`. [`Decimal256(S)`](/docs/sql-reference/data-types/decimal) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT toDecimal256OrNull('42.7', 2), toDecimal256OrNull('invalid', 2)
```

```
┌─toDecimal256OrNull('42.7', 2)─┬─toDecimal256OrNull('invalid', 2)─┐
│                         42.70 │                             ᴺᵁᴸᴸ │
└───────────────────────────────┴──────────────────────────────────┘
```

## toDecimal256OrZero[​](#toDecimal256OrZero "Direct link to toDecimal256OrZero")

Introduced in: v20.8.0

Converts an input value to a value of type [Decimal(76, S)](/docs/sql-reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal256`](#toDecimal256) but returns `0` instead of throwing an exception on conversion errors.

Supported arguments:

- Values or string representations of type (U)Int\*.
- Values or string representations of type Float\*.

Unsupported arguments (return `0`):

- Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
- String representations of binary and hexadecimal values.

Note

If the input value exceeds the bounds of `Decimal256`:`(-1*10^(76 - S), 1*10^(76 - S))`, the function returns `0`.

See also:

- [`toDecimal256`](#toDecimal256).
- [`toDecimal256OrNull`](#toDecimal256OrNull).
- [`toDecimal256OrDefault`](#toDecimal256OrDefault).

**Syntax**

```
toDecimal256OrZero(expr, S)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `S` — Scale parameter between 0 and 76, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Decimal(76, S) value if successful, otherwise `0`. [`Decimal256(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

```
SELECT toDecimal256OrZero('42.7', 2), toDecimal256OrZero('invalid', 2)
```

```
┌─toDecimal256OrZero('42.7', 2)─┬─toDecimal256OrZero('invalid', 2)─┐
│                         42.70 │                             0.00 │
└───────────────────────────────┴──────────────────────────────────┘
```

## toDecimal32[​](#toDecimal32 "Direct link to toDecimal32")

Introduced in: v18.12.0

Converts an input value to a value of type [`Decimal(9, S)`](/docs/sql-reference/data-types/decimal) with scale of `S`. Throws an exception in case of an error.

Supported arguments:

- Values or string representations of type (U)Int\*.
- Values or string representations of type Float\*.

Unsupported arguments:

- Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
- String representations of binary and hexadecimal values, e.g. `SELECT toDecimal32('0xc0fe', 1);`.

Note

An overflow can occur if the value of `expr` exceeds the bounds of `Decimal32`:`(-1*10^(9 - S), 1*10^(9 - S))`.
Excessive digits in a fraction are discarded (not rounded).
Excessive digits in the integer part will lead to an exception.

Note

Conversions drop extra digits and could operate in an unexpected way when working with Float32/Float64 inputs as the operations are performed using floating point instructions.
For example: `toDecimal32(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal32('1.15', 2) = 1.15`

**Syntax**

```
toDecimal32(expr, S)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of type `Decimal(9, S)` [`Decimal32(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

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

```
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)
```

## toDecimal32OrDefault[​](#toDecimal32OrDefault "Direct link to toDecimal32OrDefault")

Introduced in: v21.11.0

Like [`toDecimal32`](#toDecimal32), this function converts an input value to a value of type [Decimal(9, S)](/docs/sql-reference/data-types/decimal) but returns the default value in case of an error.

**Syntax**

```
toDecimal32OrDefault(expr, S[, default])
```

**Arguments**

- `expr` — A String representation of a number. [`String`](/docs/sql-reference/data-types/string)
- `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `default` — Optional. The default value to return if parsing to type Decimal32(S) is unsuccessful. [`Decimal32(S)`](/docs/sql-reference/data-types/decimal)

**Returned value**

Value of type Decimal(9, S) if successful, otherwise returns the default value if passed or 0 if not. [`Decimal32(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Successful conversion**

```
SELECT toDecimal32OrDefault(toString(0.0001), 5)
```

```
0.0001
```

**Failed conversion**

```
SELECT toDecimal32OrDefault('Inf', 0, CAST('-1', 'Decimal32(0)'))
```

```
-1
```

## toDecimal32OrNull[​](#toDecimal32OrNull "Direct link to toDecimal32OrNull")

Introduced in: v20.1.0

Converts an input value to a value of type [`Decimal(9, S)`](/docs/sql-reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal32`](#toDecimal32) but returns `NULL` instead of throwing an exception on conversion errors.

Supported arguments:

- Values or string representations of type (U)Int\*.
- Values or string representations of type Float\*.

Unsupported arguments (return `NULL`):

- Values or string representations of Float\* values `NaN` and `Inf` (case-insensitive).
- String representations of binary and hexadecimal values.
- Values that exceed the bounds of `Decimal32`:`(-1*10^(9 - S), 1*10^(9 - S))`.

See also:

- [`toDecimal32`](#toDecimal32).
- [`toDecimal32OrZero`](#toDecimal32OrZero).
- [`toDecimal32OrDefault`](#toDecimal32OrDefault).

**Syntax**

```
toDecimal32OrNull(expr, S)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)
- `S` — Scale parameter between 0 and 9, specifying how many digits the fractional part of a number can have. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Decimal(9, S) value if successful, otherwise `NULL`. [`Decimal32(S)`](/docs/sql-reference/data-types/decimal) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT toDecimal32OrNull('42.7', 2), toDecimal32OrNull('invalid', 2)
```