Type conversion functions

**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:

- 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 `Decimal64`:`(-1*10^(18 - S), 1*10^(18 - S))`, the function returns `0`.

See also:

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

**Syntax**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v23.3.0

Converts a numeric value to a String with specified number of fractional digits.

The function rounds the input value to the specified number of decimal places. If the input value has fewer fractional
digits than requested, the result is padded with zeros to achieve the exact number of fractional digits specified.

**Syntax**

```
toDecimalString(number, scale)
```

**Arguments**

- `number` — The numeric value to convert to a string. Can be any numeric type (Int, UInt, Float, Decimal). [`Int8`](/docs/sql-reference/data-types/int-uint) or [`Int16`](/docs/sql-reference/data-types/int-uint) or [`Int32`](/docs/sql-reference/data-types/int-uint) or [`Int64`](/docs/sql-reference/data-types/int-uint) or [`UInt8`](/docs/sql-reference/data-types/int-uint) or [`UInt16`](/docs/sql-reference/data-types/int-uint) or [`UInt32`](/docs/sql-reference/data-types/int-uint) or [`UInt64`](/docs/sql-reference/data-types/int-uint) or [`Float32`](/docs/sql-reference/data-types/float) or [`Float64`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `scale` — The number of digits to display in the fractional part. The result will be rounded if necessary. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a String representation of the number with exactly the specified number of fractional digits. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Round and format a number**

```
SELECT toDecimalString(2.1456, 2)
```

```
┌─toDecimalString(2.1456, 2)─┐
│ 2.15                       │
└────────────────────────────┘
```

**Pad with zeros**

```
SELECT toDecimalString(5, 3)
```

```
┌─toDecimalString(5, 3)─┐
│ 5.000                 │
└───────────────────────┘
```

**Different numeric types**

```
SELECT toDecimalString(CAST(123.456 AS Decimal(10,3)), 2) AS decimal_val,
       toDecimalString(CAST(42.7 AS Float32), 4) AS float_val
```

```
┌─decimal_val─┬─float_val─┐
│ 123.46      │ 42.7000   │
└─────────────┴───────────┘
```

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

Introduced in: v1.1.0

Converts a [`String`](/docs/sql-reference/data-types/string) argument to a [`FixedString(N)`](/docs/sql-reference/data-types/fixedstring) type (a string of fixed length N).

If the string has fewer bytes than N, it is padded with null bytes to the right.
If the string has more bytes than N, an exception is thrown.

**Syntax**

```
toFixedString(s, N)
```

**Arguments**

- `s` — String to convert. [`String`](/docs/sql-reference/data-types/string)
- `N` — Length of the resulting FixedString. [`const UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a FixedString of length N. [`FixedString(N)`](/docs/sql-reference/data-types/fixedstring)

**Examples**

**Usage example**

```
SELECT toFixedString('foo', 8) AS s;
```

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

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

Introduced in: v1.1.0

Converts an input value to a value of type [Float32](/docs/sql-reference/data-types/float).
Throws an exception in case of an error.

Supported arguments:

- Values of type (U)Int\*.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float\*, including `NaN` and `Inf`.
- String representations of Float\*, including `NaN` and `Inf` (case-insensitive).

Unsupported arguments:

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

See also:

- [`toFloat32OrZero`](#toFloat32OrZero).
- [`toFloat32OrNull`](#toFloat32OrNull).
- [`toFloat32OrDefault`](#toFloat32OrDefault).

**Syntax**

```
toFloat32(expr)
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression)

**Returned value**

Returns a 32-bit floating point value. [`Float32`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT
    toFloat32(42.7),
    toFloat32('42.7'),
    toFloat32('NaN')
FORMAT Vertical
```

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

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

Introduced in: v21.11.0

Like [`toFloat32`](#toFloat32), this function converts an input value to a value of type [Float32](/docs/sql-reference/data-types/float) but returns the default value in case of an error.
If no `default` value is passed then `0` is returned in case of an error.

**Syntax**

```
toFloat32OrDefault(expr[, default])
```

**Arguments**

- `expr` — Expression returning a number or a string representation of a number. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `default` — Optional. The default value to return if parsing is unsuccessful. [`Float32`](/docs/sql-reference/data-types/float)

**Returned value**

Returns a value of type Float32 if successful, otherwise returns the default value if passed or 0 if not. [`Float32`](/docs/sql-reference/data-types/float)

**Examples**

**Successful conversion**

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

```
8
```

**Failed conversion**

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

```
0
```

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

Introduced in: v1.1.0

Converts an input value to a value of type [Float32](/docs/sql-reference/data-types/float) but returns `NULL` in case of an error.
Like [`toFloat32`](#toFloat32) but returns `NULL` instead of throwing an exception on conversion errors.

Supported arguments:

- Values of type (U)Int\*.
- String representations of (U)Int8/16/32/128/256.
- Values of type Float\*, including `NaN` and `Inf`.
- String representations of Float\*, including `NaN` and `Inf` (case-insensitive).

Unsupported arguments (return `NULL`):