Skip to main content

Celonis Product Documentation

TO_FLOAT
Description

TO_FLOAT converts STRING input to FLOAT output. If the input does not represent a float number (or the value of the representation is outside of the supported FLOAT range), the function returns NULL.

Supported input column type: STRING. TO_FLOAT supports the decimal separator and scientific E notation in input strings. Numbers are parsed according to the "en_US.UTF-8" numeric locale. That is, using "." as the decimal separator and "," as the thousands separator. Whitespace is not allowed in the input string.

Output column type: FLOAT.

Syntax
 TO_FLOAT ( table.column )
Null handling

If the input value is NULL, the output value is NULL as well.

Example

[1]

Valid string to float conversions using fixed point notation. Input strings contain a mix of:

  • "+" and "-" signs

  • Decimal "." separator

  • Thousands "," separator

Query

Column1

         TO_FLOAT ( "Table1"."Column1" )
        

Input

Output

Table1

Column1 : string

'+123456'

'-123456'

'+00003'

'3.'

'1.11'

'-9.99'

'9223372036854775807'

'-9223372036854775808'

'2,500.10'

'-2,500.10'

Result

Column1 : float

123456.0

-123456.0

3.0

3.0

1.11

-9.99

9.223372036854776E18

-9.223372036854776E18

2500.1

-2500.1

[2]

Valid string to float conversions using scientific E notation. Input strings contain a mix of:

  • "+" and "-" signs

  • Decimal "." separator

  • Thousands "," separator

Query

Column1

         TO_FLOAT ( "Table1"."Column1" )
        

Input

Output

Table1

Column1 : string

'4,000.0e2'

'4,000.0e2'

'-5.93E-2'

'-5.93e-2'

'2e0'

'2e+00'

Result

Column1 : float

400000.0

400000.0

-0.0593

-0.0593

2.0

2.0

[3]

These invalid string inputs are mapped to NULL.

  • NULL

  • The empty string

  • Invalid float representation (for example, unexpected character 'F')

  • Presence of whitespace characters

  • Values outside the range for FLOAT

  • Multiple "+" or "-" signs

  • Missing scientific E notation exponent

  • Invalid scientific E notation exponent

  • Multiple decimal separators

  • Invalid number of digits between the thousand separator ","

  • Thousands separator in the fractional part

  • Infinity (INF) and Not a Number (NaN) strings

Query

Column1

         TO_FLOAT ( "Table1"."Column1" )
        

Input

Output

Table1

Column1 : string

null

''

'F10.0'

'10.F0'

'10.0F'

' 321'

'3 21'

'321 '

'1E650'

'++1'

'--1'

'1E'

'1EA'

'1.0.0'

'10,00,000'

'10,0000,000'

'1.234,5'

'INF'

'NaN'

Result

Column1

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

Warning

TO_FLOAT: The input column contains strings that do not conform to a valid float format. In these cases, returning NULL instead. Affected strings: '', ' 321', '++1', '--1', '1.0.0', ...

See also: