to_integer

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

## Syntax

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

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

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

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

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

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

```sql
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 |

## Related articles

* **Stages**: [`alter`](../stages/alter), [`filter`](../stages/filter), [`fields`](../stages/fields), [`config`](../stages/config), [`limit`](../stages/limit)
* **Functions**: [`to_string`](to_string), [`to_number`](to_number), [`to_float`](to_float), [`split`](split), [`arrayindex`](arrayindex)