elasticsearch/docs/reference/esql/esql-functions.asciidoc

[[esql-functions]]
== Functions

<<esql-row,`ROW`>>, <<esql-eval,`EVAL`>> and <<esql-where,`WHERE`>> support
these functions:

* <<esql-abs>>
* <<esql-case>>
* <<esql-concat>>
* <<esql-date_format>>
* <<esql-date_trunc>>
* <<esql-is_null>>
* <<esql-length>>
* <<esql-round>>
* <<esql-starts_with>>
* <<esql-substring>>

[[esql-abs]]
=== `ABS`
Returns the absolute value.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL abs_height = ABS(0.0 - height)
----

[[esql-case]]
=== `CASE`

Accepts pairs of conditions and values. The function returns the value that
belongs to the first condition that evaluates to `true`. If the number of
arguments is odd, the last argument is the default value which is returned when
no condition matches.

[source,esql]
----
FROM employees
| EVAL type = CASE(
    languages <= 1, "monolingual",
    languages <= 2, "bilingual",
     "polyglot")
| PROJECT first_name, last_name, type
----

[[esql-concat]]
=== `CONCAT`
Concatenates two or more strings.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL fullname = CONCAT(first_name, " ", last_name)
----

[[esql-date_format]]
=== `DATE_FORMAT`
Returns a string representation of a date in the provided format. If no format
is specified, the `yyyy-MM-dd'T'HH:mm:ss.SSSZ` format is used.

[source,esql]
----
FROM employees 
| PROJECT first_name, last_name, hire_date
| EVAL hired = DATE_FORMAT(hire_date, "YYYY-MM-dd")
----

[[esql-date_trunc]]
=== `DATE_TRUNC`
Rounds down a date to the closest interval. Intervals can be expressed using the
<<esql-timespan-literals,timespan literal syntax>>.

[source,esql]
----
FROM employees
| EVAL year_hired = DATE_TRUNC(hire_date, 1 year)
| STATS count(emp_no) BY year_hired
| SORT year_hired
----

[[esql-is_finite]]
=== `IS_FINITE`
Returns a boolean that indicates whether its input is a finite number.

[source,esql]
----
ROW d = 1.0 
| EVAL s = IS_FINITE(d/0)
----

[[esql-is_infinite]]
=== `IS_INFINITE`
Returns a boolean that indicates whether its input is infinite.

[source,esql]
----
ROW d = 1.0 
| EVAL s = IS_INFINITE(d/0)
----

[[esql-is_nan]]
=== `IS_NAN`
Returns a boolean that indicates whether its input is not a number.

[source,esql]
----
ROW d = 1.0 
| EVAL s = IS_NAN(d)
----

[[esql-is_null]]
=== `IS_NULL`
Returns a boolean than indicates whether its input is `null`.

[source,esql]
----
FROM employees
| WHERE is_null(first_name)
----

Combine this function with `NOT` to filter out any `null` data:

[source,esql]
----
FROM employees
| WHERE NOT is_null(first_name)
----

[[esql-length]]
=== `LENGTH`
Returns the character length of a string.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL fn_length = LENGTH(first_name)
----

[[esql-pow]]
=== `POW`
Returns the the value of a base (first argument) raised to a power (second 
argument).

[source,esql]
----
ROW base = 2.0, exponent = 2.0 
| EVAL s = POW(base, exponent)
----

[[esql-round]]
=== `ROUND`
Rounds a number to the closest number with the specified number of digits.
Defaults to 0 digits if no number of digits is provided. If the specified number
of digits is negative, rounds to the number of digits left of the decimal point.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL height = ROUND(height * 3.281, 1)
----

[[esql-starts_with]]
=== `STARTS_WITH`
Returns a boolean that indicates whether a keyword string starts with another 
string:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL ln_S = STARTS_WITH(last_name, "S")
----

[[esql-substring]]
=== `SUBSTRING`
Returns a substring of a string, specified by a start position and an optional
length. This example returns the first three characters of every last name:

[source,esql]
----
FROM employees
| PROJECT last_name
| EVAL ln_sub = SUBSTRING(last_name, 1, 3) 
----

A negative start position is interpreted as being relative to the end of the
string. This example returns the last three characters of of every last name:

[source,esql]
----
FROM employees
| PROJECT last_name
| EVAL ln_sub = SUBSTRING(last_name, -3, 3) 
----

If length is omitted, substring returns the remainder of the string. This
example returns all characters except for the first:

[source,esql]
----
FROM employees
| PROJECT last_name
| EVAL ln_sub = SUBSTRING(last_name, 2) 
----