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. Trailing and leading whitespaces will be automatically removed and the trimmed STRING will be converted to a FLOAT. Other whitespaces are 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'

' 1.02'

'-2.1 '

' 45.2 '

Result

Column1 : float

123456.0

-123456.0

3.0

3.0

1.11

-9.99

9.223372036854776E18

-9.223372036854776E18

2500.1

-2500.1

1.02

-2.1

45.2

[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'

'3 21'

'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

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: '', '++1', '--1', '1.0.0', '1.234,5', ...

See also: