---
title: Functions and operators
description: Elasticsearch SQL provides a comprehensive set of built-in operators and functions: Operators, LIKE and RLIKE Operators, Aggregate Functions, Grouping...
url: https://www.elastic.co/docs/reference/query-languages/sql/sql-functions
products:
  - Elasticsearch
---

# Functions and operators
Elasticsearch SQL provides a comprehensive set of built-in operators and functions:
- [Operators](https://www.elastic.co/docs/reference/query-languages/sql/sql-operators)
  - [`Equality (=)`](/docs/reference/query-languages/sql/sql-operators#sql-operators-equality)
  - [`Null safe Equality (<=>)`](/docs/reference/query-languages/sql/sql-operators#sql-operators-null-safe-equality)
  - [`Inequality (<> or !=)`](/docs/reference/query-languages/sql/sql-operators#sql-operators-inequality)
  - [`Comparison (<, <=, >, >=)`](/docs/reference/query-languages/sql/sql-operators#sql-operators-comparison)
  - [`BETWEEN`](/docs/reference/query-languages/sql/sql-operators#sql-operators-between)
  - [`IS NULL/IS NOT NULL`](/docs/reference/query-languages/sql/sql-operators#sql-operators-is-null)
  - [`IN (<value1>, <value2>, ...)`](/docs/reference/query-languages/sql/sql-operators#sql-operators-in)
  - [`AND`](/docs/reference/query-languages/sql/sql-operators-logical#sql-operators-and)
  - [`OR`](/docs/reference/query-languages/sql/sql-operators-logical#sql-operators-or)
  - [`NOT`](/docs/reference/query-languages/sql/sql-operators-logical#sql-operators-not)
  - [`Add (+)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-plus)
  - [`Subtract (infix -)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-subtract)
  - [`Negate (unary -)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-negate)
  - [`Multiply (*)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-multiply)
  - [`Divide (/)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-divide)
  - [`Modulo or Remainder(%)`](/docs/reference/query-languages/sql/sql-operators-math#sql-operators-remainder)
  - [`Cast (::)`](/docs/reference/query-languages/sql/sql-operators-cast#sql-operators-cast-cast)
- [LIKE and RLIKE Operators](https://www.elastic.co/docs/reference/query-languages/sql/sql-like-rlike-operators)
  - [`LIKE`](/docs/reference/query-languages/sql/sql-like-rlike-operators#sql-like-operator)
  - [`RLIKE`](/docs/reference/query-languages/sql/sql-like-rlike-operators#sql-rlike-operator)
- [Aggregate Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-aggs)
  - [`AVG`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-avg)
  - [`COUNT`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-count)
  - [`COUNT(ALL)`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-count-all)
  - [`COUNT(DISTINCT)`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-count-distinct)
  - [`FIRST/FIRST_VALUE`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-first)
  - [`LAST/LAST_VALUE`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-last)
  - [`MAX`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-max)
  - [`MIN`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-min)
  - [`SUM`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-sum)
  - [`KURTOSIS`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-kurtosis)
  - [`MAD`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-mad)
  - [`PERCENTILE`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-percentile)
  - [`PERCENTILE_RANK`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-percentile-rank)
  - [`SKEWNESS`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-skewness)
  - [`STDDEV_POP`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-stddev-pop)
  - [`STDDEV_SAMP`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-stddev-samp)
  - [`SUM_OF_SQUARES`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-sum-squares)
  - [`VAR_POP`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-var-pop)
  - [`VAR_SAMP`](/docs/reference/query-languages/sql/sql-functions-aggs#sql-functions-aggs-var-samp)
- [Grouping Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-grouping)
  - [`HISTOGRAM`](/docs/reference/query-languages/sql/sql-functions-grouping#sql-functions-grouping-histogram)
- [Date-Time Operators](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-interval)
- [Date-Time Functions](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-current-date)
  - [`CURRENT_DATE/CURDATE`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-current-date)
  - [`CURRENT_TIME/CURTIME`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-current-time)
  - [`CURRENT_TIMESTAMP`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-current-timestamp)
  - [`DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-add)
  - [`DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-diff)
  - [`DATE_FORMAT`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-dateformat)
  - [`DATE_PARSE`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-dateparse)
  - [`DATETIME_FORMAT`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-datetimeformat)
  - [`DATETIME_PARSE`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-datetimeparse)
  - [`FORMAT`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-format)
  - [`DATE_PART/DATEPART`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-part)
  - [`DATE_TRUNC/DATETRUNC`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-trunc)
  - [`DAY_OF_MONTH/DOM/DAY`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-day)
  - [`DAY_OF_WEEK/DAYOFWEEK/DOW`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-dow)
  - [`DAY_OF_YEAR/DOY`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-doy)
  - [`DAY_NAME/DAYNAME`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-dayname)
  - [`EXTRACT`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-extract)
  - [`HOUR_OF_DAY/HOUR`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-hour)
  - [`ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-isodow)
  - [`ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-isoweek)
  - [`MINUTE_OF_DAY`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-minuteofday)
  - [`MINUTE_OF_HOUR/MINUTE`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-minute)
  - [`MONTH_OF_YEAR/MONTH`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-month)
  - [`MONTH_NAME/MONTHNAME`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-monthname)
  - [`NOW`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-now)
  - [`SECOND_OF_MINUTE/SECOND`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-second)
  - [`QUARTER`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-quarter)
  - [`TIME_PARSE`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-timeparse)
  - [`TO_CHAR`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-to_char)
  - [`TODAY`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-today)
  - [`WEEK_OF_YEAR/WEEK`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-week)
  - [`YEAR`](/docs/reference/query-languages/sql/sql-functions-datetime#sql-functions-datetime-year)
- [Full-Text Search Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-search)
  - [`MATCH`](/docs/reference/query-languages/sql/sql-functions-search#sql-functions-search-match)
  - [`QUERY`](/docs/reference/query-languages/sql/sql-functions-search#sql-functions-search-query)
  - [`SCORE`](/docs/reference/query-languages/sql/sql-functions-search#sql-functions-search-score)
- [Mathematical Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-math)
  - [`ABS`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-abs)
  - [`CBRT`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-cbrt)
  - [`CEIL/CEILING`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-ceil)
  - [`E`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-e)
  - [`EXP`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-exp)
  - [`EXPM1`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-expm1)
  - [`FLOOR`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-floor)
  - [`LOG`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-log)
  - [`LOG10`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-log10)
  - [`PI`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-pi)
  - [`POWER`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-power)
  - [`RANDOM/RAND`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-random)
  - [`ROUND`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-round)
  - [`SIGN/SIGNUM`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-sign)
  - [`SQRT`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-sqrt)
  - [`TRUNCATE/TRUNC`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-truncate)
  - [`ACOS`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-acos)
  - [`ASIN`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-asin)
  - [`ATAN`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-atan)
  - [`ATAN2`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-atan2)
  - [`COS`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-cos)
  - [`COSH`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-cosh)
  - [`COT`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-cot)
  - [`DEGREES`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-degrees)
  - [`RADIANS`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-radians)
  - [`SIN`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-sin)
  - [`SINH`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-sinh)
  - [`TAN`](/docs/reference/query-languages/sql/sql-functions-math#sql-functions-math-tan)
- [String Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-string)
  - [`ASCII`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-ascii)
  - [`BIT_LENGTH`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-bit-length)
  - [`CHAR`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-char)
  - [`CHAR_LENGTH`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-char-length)
  - [`CONCAT`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-concat)
  - [`INSERT`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-insert)
  - [`LCASE`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-lcase)
  - [`LEFT`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-left)
  - [`LENGTH`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-length)
  - [`LOCATE`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-locate)
  - [`LTRIM`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-ltrim)
  - [`OCTET_LENGTH`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-octet-length)
  - [`POSITION`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-position)
  - [`REPEAT`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-repeat)
  - [`REPLACE`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-replace)
  - [`RIGHT`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-right)
  - [`RTRIM`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-rtrim)
  - [`SPACE`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-space)
  - [`SUBSTRING`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-substring)
  - [`TRIM`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-trim)
  - [`UCASE`](/docs/reference/query-languages/sql/sql-functions-string#sql-functions-string-ucase)
- [Type Conversion Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-type-conversion)
  - [`CAST`](/docs/reference/query-languages/sql/sql-functions-type-conversion#sql-functions-type-conversion-cast)
  - [`CONVERT`](/docs/reference/query-languages/sql/sql-functions-type-conversion#sql-functions-type-conversion-convert)
- [Conditional Functions And Expressions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-conditional)
  - [`CASE`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-case)
  - [`COALESCE`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-coalesce)
  - [`GREATEST`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-greatest)
  - [`IFNULL`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-ifnull)
  - [`IIF`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-iif)
  - [`ISNULL`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-isnull)
  - [`LEAST`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-least)
  - [`NULLIF`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-nullif)
  - [`NVL`](/docs/reference/query-languages/sql/sql-functions-conditional#sql-functions-conditional-nvl)
- [Geo Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-geo)
  - [`ST_AsWKT`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-as-wkt)
  - [`ST_Distance`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-distance)
  - [`ST_GeometryType`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-geometrytype)
  - [`ST_WKTToSQL`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-wkt-to-sql)
  - [`ST_X`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-x)
  - [`ST_Y`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-y)
  - [`ST_Z`](/docs/reference/query-languages/sql/sql-functions-geo#sql-functions-geo-st-z)
- [System Functions](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-system)
  - [`DATABASE`](/docs/reference/query-languages/sql/sql-functions-system#sql-functions-system-database)
  - [`USER`](/docs/reference/query-languages/sql/sql-functions-system#sql-functions-system-user)