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