to_number ↗

Use the to_number() function to convert a string value that represents a number or a numeric data type into a floating-point number.

Syntax

to_number (<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_number() function returns a floating-point number.

Usage notes

  • The function requires a string value that represents a number or a numeric data type.
  • The function is functionally identical to the to_float() function.
  • The function consistently returns a floating-point number. Even if the input string represents a whole integer (for example, "200"), the output will be a floating-point number (for example, 200.0).
  • If the input string cannot be successfully converted to a numeric value (for example, it contains letters or unparseable characters), the function will return NULL.
  • If the input string itself is NULL, the function will return NULL.

Examples

Example 1: Converting a literal integer string

Goal: Convert a string literal representing an integer into a numerical (float) value.

XQL code:

config timeframe = 1d // Sets the query time context 
| dataset = sample_xql_raw // Specifies the dataset to use 
| alter converted_integer_string = to_number("42") // Converts a literal string "42" to a number 
| fields event_id, converted_integer_string // Selects relevant fields for display 
| limit 3 // Limits the output for brevity

Explanation: The literal string "42" is successfully converted by to_number() into a floating-point number 42.0.

Output:

EVENT_ID CONVERTED_INTEGER_STRING
101 42.0
102 42.0
103 42.0

Example 2: Converting a literal floating-point string

Goal: Convert a string literal representing a decimal number into a floating-point value.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter converted_float_string = to_number("123.45") // Converts a literal string "123.45" to a number 
| fields event_id, converted_float_string 
| limit 3

Explanation: The literal string "123.45" is correctly converted into the floating-point number 123.45.

Output:

EVENT_ID CONVERTED_FLOAT_STRING
101 123.45
102 123.45
103 123.45

Example 3: Converting a numeric string extracted from a JSON field

Goal: Extract a numerical value from a JSON string field and convert it from its string representation to a number.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter extracted_code_as_string = simple_json_data -> code // Extracts 'code' as a string 
| alter converted_json_code = to_number(extracted_code_as_string) // Converts the extracted string to a number 
| fields event_id, simple_json_data, converted_json_code 
| limit 3

Explanation: For event_id 101, the "code" value "200" is extracted as a string and then successfully converted to the number 200.0. For event_id 102 and 103, where simple_json_data does not contain a "code" field, extracted_code_as_string becomes NULL, and subsequently, converted_json_code also becomes NULL.

Output:

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

Example 4: Attempting to convert a string with non-numeric characters

Goal: Demonstrate the behavior when attempting to convert a string that contains non-numerical characters.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter mem_free_string = nested_json_data -> system.mem_free // Extracts "80%" as a string 
| alter converted_mem_free = to_number(mem_free_string) // Attempts to convert "80%" to a number 
| fields event_id, nested_json_data, converted_mem_free 
| filter event_id = 104 // Focus on the relevant record for this example 
| limit 1

Explanation: to_number() cannot perform the conversion and returns NULL for invalid inputs because "80%" is not a pure numeric string.

Output:

EVENT_ID NESTED_JSON_DATA CONVERTED_MEM_FREE
104 {"system": {"cpu_util": 0.15, "mem_free": "80%"}, "status": "active"} NULL

Example 5: Converting a NULL input field

Goal: Demonstrate the behavior when the input field is explicitly NULL.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw
| filter event_id = 105 // Focus on the relevant record for this example
| alter null_input_field = dst_domain // This field is NULL for some records (for example, event_id 105) 
| alter converted_null = to_number(null_input_field) // Attempts to convert a NULL field to a number 
| fields event_id, dst_domain, converted_null 
| limit 1

Explanation: When the input to to_number() is NULL, the function consistently returns NULL.

Output:

EVENT_ID DST_DOMAIN CONVERTED_NULL
105 NULL NULL