Type conversion functions

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

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

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

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 `0` in case of an error.
Like [`toDecimal32`](#toDecimal32) 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 `Decimal32`:`(-1*10^(9 - S), 1*10^(9 - S))`, the function returns `0`.

**Syntax**

```
toDecimal32OrZero(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 `0`. [`Decimal32(S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

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

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

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

Introduced in: v18.12.0

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

Note

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

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: `toDecimal64(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: `toDecimal64('1.15', 2) = 1.15`

**Syntax**

```
toDecimal64(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 18, 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 value. [`Decimal(18, S)`](/docs/sql-reference/data-types/decimal)

**Examples**

**Usage example**

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

```
Row 1:
──────
a:      2.0
type_a: Decimal(18, 1)
b:      4.20
type_b: Decimal(18, 2)
c:      4.200
type_c: Decimal(18, 3)
```

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

Introduced in: v21.11.0

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

**Syntax**

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

**Arguments**

- `expr` — A String representation of a number. [`String`](/docs/sql-reference/data-types/string)
- `S` — Scale parameter between 0 and 18, 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 Decimal64(S) is unsuccessful. [`Decimal64(S)`](/docs/sql-reference/data-types/decimal)

**Returned value**

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

**Examples**

**Successful conversion**

```
SELECT toDecimal64OrDefault(toString(0.0001), 18)
```

```
0.0001
```

**Failed conversion**

```
SELECT toDecimal64OrDefault('Inf', 0, CAST('-1', 'Decimal64(0)'))
```

```
-1
```

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

Introduced in: v20.1.0

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

See also:

- [`toDecimal64`](#toDecimal64).
- [`toDecimal64OrZero`](#toDecimal64OrZero).
- [`toDecimal64OrDefault`](#toDecimal64OrDefault).

**Syntax**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v20.1.0

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

Supported arguments: