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