to_integer ↗

Use the to_integer() function to convert a string representation of a number into an integer value.

Syntax

to_integer(<string>)

Parameters

Name Type Required Description
string string Yes The string field or literal value that you wish to convert to a number.

Returns

The to_integer() function returns an integer.

Usage notes

  • When a string representation of a floating-point number (for example, "1.5") is provided as input, the function returns NULL. This differentiates its behavior from functions like floor() or round().
  • If passed a numeric data type (like a float), the result will be rounded up or down to the nearest integer.
  • If the input string does not represent a valid number, or if the input itself is NULL, the function returns NULL.

Examples

Example 1: Converting a literal integer string to integer

Goal: Convert a string literal that represents a whole number directly into an integer.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter literal_integer_val = to_integer("42")
| fields event_id, literal_integer_val
| limit 3

Explanation: The literal string "42" is successfully converted by to_integer() into the integer value 42.

Output:

EVENT_ID LITERAL_INTEGER_VAL
101 42
102 42
103 42

Example 2: Converting a literal floating-point string to integer

Goal: Demonstrate behavior when provided with a string that represents a floating-point number.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter float_string_to_int = to_integer("123.789")
| fields event_id, float_string_to_int
| limit 3

Explanation: When a string containing a decimal (floating-point representation) is passed to to_integer(), it results in a NULL output.

Output:

EVENT_ID FLOAT_STRING_TO_INT
101 NULL
102 NULL
103 NULL

Example 3: Converting a numeric string from an existing field to integer

Goal: Convert an existing floating-point field to an integer, demonstrating automatic rounding.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter integer_duration = to_integer(duration_seconds)
| fields event_id, duration_seconds, integer_duration
| limit 3

Explanation: The duration_seconds field is a floating-point number. When to_integer() is applied to a numeric type, it automatically rounds the number to the nearest integer (for example, 1.5 becomes 2, 0.8 becomes 1).

Output:

EVENT_ID DURATION_SECONDS INTEGER_DURATION
101 1.5 2
102 0.8 1
103 10.2 10

Example 4: Converting a string extracted from JSON to integer

Goal: Extract a numeric value from a JSON string field and convert it to an integer.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter json_code_string = simple_json_data -> code
| alter parsed_json_code = to_integer(json_code_string)
| fields event_id, simple_json_data, json_code_string, parsed_json_code
| limit 3

Explanation: For event 101, the "code" value "200" is extracted as a string and converted to the integer 200. For events where the field is missing, the result is NULL.

Output:

EVENT_ID SIMPLE_JSON_DATA JSON_CODE_STRING PARSED_JSON_CODE
101 {"status": "ok", "code": 200} "200" 200
102 {"status": "fail", "error": "access_denied"} NULL NULL
103 {"connection_id": "CONN-001", "protocol": "TCP"} NULL NULL

Example 5: Handling non-numeric string input

Goal: Demonstrate behavior when providing a string that cannot be interpreted as a number.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter non_numeric_string = event_description
| alter int_conversion_result = to_integer(non_numeric_string)
| fields event_id, non_numeric_string, int_conversion_result
| limit 3

Explanation: The conversion returns NULL because event_description contains text (for example, "User login successful") and not a valid number.

Output:

EVENT_ID NON_NUMERIC_STRING INT_CONVERSION_RESULT
101 "User login successful" NULL
102 "File access attempt" NULL
103 "Network connection established" NULL

Example 6: Using to_integer() in a filter stage

Goal: Extract a numeric value from a string field, convert it to an integer, and use it for numerical comparison in a filter.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter first_octet_string = arrayindex(split(ipv4_address, "."), 0)
| alter first_octet_int = to_integer(first_octet_string)
| filter first_octet_int > 192
| fields event_id, ipv4_address, first_octet_string, first_octet_int
| limit 3

Explanation: The query splits the IP address to isolate the first octet as a string. to_integer() converts this string to an integer, allowing the filter stage to numerically compare if the octet is greater than 192.

Output:

EVENT_ID IPV4_ADDRESS FIRST_OCTET_STRING FIRST_OCTET_INT
106 "203.0.113.15" "203" 203