Loading

ES|QL TO_INTEGER function

Embedded
field
Input value. The input can be a single- or multi-valued column or an expression.
base

(Optional) Radix or base used to convert the input value.When a base is specified the input type must be keyword or text.

Converts an input value to an integer value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to integer. Boolean true will be converted to integer 1, false to 0.

When given two arguments, a string value and a whole number base, the string is parsed as an integer in the given base. If parsing fails a warning is generated as described below and the result is null. A leading '0x' prefix is allowed for base 16.

field base result
boolean integer
counter_integer integer
date integer
double integer
integer integer
keyword integer integer
keyword long integer
keyword unsigned_long integer
keyword integer
long integer
text integer integer
text long integer
text unsigned_long integer
text integer
unsigned_long integer 
ROW long = [5013792, 2147483647, 501379200000]
| EVAL int = TO_INTEGER(long)
long:long int:integer
[5013792, 2147483647, 501379200000] [5013792, 2147483647]

Note that in this example, the last value of the multi-valued field cannot be converted as an integer. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:61: evaluation of [TO_INTEGER(long)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"org.elasticsearch.xpack.esql.core.InvalidArgumentException: [501379200000] out of [integer] range"

ROW str1 = "0x32", str2 = "31"
| EVAL int1 = TO_INTEGER(str1, 16), int2 = TO_INTEGER(str2, 13)
| KEEP str1, int1, str2, int2
str1:keyword int1:integer str2:keyword int2:integer
0x32 50 31 40

This example demonstrates parsing a base 16 value and a base 13 value. 

ROW str1 = "Kona"
| EVAL int1 = TO_INTEGER(str1, 27), fail1 = TO_INTEGER(str1, 10)
str1:keyword int1:integer fail1:integer
Kona 411787 null

This example demonstrates parsing a string that is valid in base 27 but invalid in base 10.Observe in the second case a warning is generated and null is returned.