Type conversion functions

**Returned value**

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

**Examples**

**Successful conversion**

```
SELECT toDecimal128OrDefault(toString(1/42), 18)
```

```
0.023809523809523808
```

**Failed conversion**

```
SELECT toDecimal128OrDefault('Inf', 0, CAST('-1', 'Decimal128(0)'))
```

```
-1
```

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

Introduced in: v20.1.0

Converts an input value to a value of type [`Decimal(38, S)`](/docs/sql-reference/data-types/decimal) but returns `NULL` in case of an error.
Like [`toDecimal128`](#toDecimal128) 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 `Decimal128`:`(-1*10^(38 - S), 1*10^(38 - S))`.

See also:

- [`toDecimal128`](#toDecimal128).
- [`toDecimal128OrZero`](#toDecimal128OrZero).
- [`toDecimal128OrDefault`](#toDecimal128OrDefault).

**Syntax**

```
toDecimal128OrNull(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 38, 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(38, S) value if successful, otherwise `NULL`. [`Decimal128(S)`](/docs/sql-reference/data-types/decimal) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

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

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

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

Introduced in: v20.1.0

Converts an input value to a value of type [Decimal(38, S)](/docs/sql-reference/data-types/decimal) but returns `0` in case of an error.
Like [`toDecimal128`](#toDecimal128) 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 `Decimal128`:`(-1*10^(38 - S), 1*10^(38 - S))`, the function returns `0`.

**Syntax**

```
toDecimal128OrZero(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 38, 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(38, S) value if successful, otherwise `0`. [`Decimal128(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Basic usage**

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

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

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

Introduced in: v20.8.0

Converts an input value to a value of type [`Decimal(76, 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 toDecimal256('0xc0fe', 1);`.

Note

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

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: `toDecimal256(1.15, 2)` is equal to `1.14` because 1.15 \* 100 in floating point is 114.99.
You can use a String input so the operations use the underlying integer type: `toDecimal256('1.15', 2) = 1.15`

**Syntax**

```
toDecimal256(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 value of type `Decimal(76, S)`. [`Decimal256(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

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

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

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

Introduced in: v21.11.0

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

**Syntax**

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

**Arguments**

- `expr` — A String representation of a number. [`String`](/docs/sql-reference/data-types/string)
- `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)
- `default` — Optional. The default value to return if parsing to type Decimal256(S) is unsuccessful. [`Decimal256(S)`](/docs/sql-reference/data-types/decimal)

**Returned value**

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

**Examples**

**Successful conversion**

```
SELECT toDecimal256OrDefault(toString(1/42), 76)
```

```
0.023809523809523808
```

**Failed conversion**

```
SELECT toDecimal256OrDefault('Inf', 0, CAST('-1', 'Decimal256(0)'))
```

```
-1
```

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

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 `NULL` in case of an error.
Like [`toDecimal256`](#toDecimal256) 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 `Decimal256`: `(-1 * 10^(76 - S), 1 * 10^(76 - S))`.

See also:

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

**Syntax**

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