substr()

The substr() function returns the substring of a string corresponding to the specified index range.

Syntax

Parameters

STR

The original string field or value. Non-string values are converted to a string before processing.

BEGIN

The character index at which extraction starts. Indices start at 0. A negative value is counted backward from the end of the string. How an invalid BEGIN is handled differs by series — see Description below. If the converted start index is greater than or equal to the string length, or is less than 0, null is returned.

END

(Optional) The character index at which extraction ends (exclusive). Indices start at 0. If omitted, extraction continues to the end of the string. A negative value is counted backward from the end of the string. If END is greater than the string length, extraction continues to the end of the string. How an invalid END is handled differs by series — see Description below. If BEGIN is greater than END, or END is less than 0 after conversion, null is returned.

Description

The substr() function returns the substring of STR from the BEGIN index (inclusive) to the END index (exclusive).

• If STR is null, the function returns null.
• If END equals BEGIN, an empty string ("") is returned.
• How an invalid BEGIN or END value is handled differs by series:
  • In the 5.0 series (through 5.0.2605.2): if BEGIN is null, the entire STR is returned. If BEGIN or END cannot be interpreted as a number, the query fails with an error and processing of the remaining rows is aborted.
  • In the 4.0 series starting with 4.0.2609.0: if BEGIN or END is null, cannot be interpreted as a number, or is outside the int range, null is returned for that row only, and the query continues processing the remaining rows. A numeric string (for example, "42") is treated as a valid index.

Error codes

N/A

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

1. Extract the first 10 characters of a URI

   table limit=5 WEB_APACHE_SAMPLE | eval result = substr(uri, 0, 10) | fields uri, result
   

2. Extract the part of a URI after the first / by specifying only the start index

   table limit=5 WEB_APACHE_SAMPLE | eval result = substr(uri, 1) | fields uri, result
   | # result: "feed", "archives/2370", etc.
   

3. Extract the end of a URI using a negative index

   table limit=5 WEB_APACHE_SAMPLE | eval result = substr(uri, -5) | fields uri, result
   

4. NULL input

   json "{'val': null}" | eval result = substr(val, 0, 3)
   | # result: null
   

5. NULL start index — result differs by series

   json "{'val': 'hello'}" | eval result = substr(val, null)
   | # 5.0 series (through 5.0.2605.2) result: hello / 4.0 series (4.0.2609.0 and later) result: null
   

   In the 5.0 series (through 5.0.2605.2), if BEGIN is null, the entire original string (val) is returned as-is. In the 4.0 series starting with 4.0.2609.0, null is returned instead.

6. Start index that cannot be interpreted as a number — result differs by series

   json "{'val': 'hello'}" | eval result = substr(val, "abc")
   

   In the 5.0 series (through 5.0.2605.2), this kind of input raises an error that aborts the entire query. In the 4.0 series starting with 4.0.2609.0, only that row returns null (result: null), and the remaining rows are processed normally.

Change history