Scalar function types at a glance

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

This article lists all available scalar functions grouped by type. For aggregation functions, see Aggregation function types.

Binary functions

Function Name	Description
binary_and()	Returns a result of the bitwise and operation between two values.
binary_not()	Returns a bitwise negation of the input value.
binary_or()	Returns a result of the bitwise or operation of the two values.
binary_shift_left()	Returns binary shift left operation on a pair of numbers: a << n.
binary_shift_right()	Returns binary shift right operation on a pair of numbers: a >> n.
binary_xor()	Returns a result of the bitwise xor operation of the two values.
bitset_count_ones()	Returns the number of set bits in the binary representation of a number.

Conversion functions

Function Name	Description
tobool()	Convert inputs to boolean (signed 8-bit) representation.
todatetime()	Converts input to datetime scalar.
todouble()	Converts the input to a value of type real.
tostring()	Converts input to a string representation.
totimespan()	Converts input to timespan scalar.

DateTime/timespan functions

Function Name	Description
ago()	Subtracts the given timespan from the current UTC clock time.
datetime_add()	Calculates a new datetime from a specified datepart multiplied by a specified amount, added to a specified datetime.
datetime_diff()	Returns the end of the year containing the date, shifted by an offset, if provided.
datetime_local_to_utc()	Converts local datetime to UTC datetime using a time-zone specification.
datetime_part()	Extracts the requested date part as an integer value.
datetime_utc_to_local()	Converts UTC datetimgoe to local datetime using a time-zone specification.
dayofmonth()	Returns the integer number representing the day number of the given month.
dayofweek()	Returns the integer number of days since the preceding Sunday, as a timespan.
dayofyear()	Returns the integer number represents the day number of the given year.
endofday()	Returns the end of the day containing the date, shifted by an offset, if provided.
endofmonth()	Returns the end of the month containing the date, shifted by an offset, if provided.
endofweek()	Returns the end of the week containing the date, shifted by an offset, if provided.
endofyear()	Returns the end of the year containing the date, shifted by an offset, if provided.
format_datetime()	Formats a datetime parameter based on the format pattern parameter.
format_timespan()	Formats a format-timespan parameter based on the format pattern parameter.
getyear()	Returns the year part of the datetime argument.
hourofday()	Returns the integer number representing the hour number of the given date.
make_datetime()	Creates a datetime scalar value from the specified date and time.
make_timespan()	Creates a timespan scalar value from the specified time period.
monthofyear()	Returns the integer number that represents the month number of the given year.
now()	Returns the current UTC clock time, optionally offset by a given timespan.
startofday()	Returns the start of the day containing the date, shifted by an offset, if provided.
startofmonth()	Returns the start of the month containing the date, shifted by an offset, if provided.
startofweek()	Returns the start of the week containing the date, shifted by an offset, if provided.
startofyear()	Returns the start of the year containing the date, shifted by an offset, if provided.
todatetime()	Converts input to datetime scalar.
totimespan()	Converts input to timespan scalar.
unixtime_microseconds_todatetime()	Converts unix-epoch microseconds to UTC datetime.
unixtime_milliseconds_todatetime()	Converts unix-epoch milliseconds to UTC datetime.
unixtime_nanoseconds_todatetime()	Converts unix-epoch nanoseconds to UTC datetime.
unixtime_seconds_todatetime()	Converts unix-epoch seconds to UTC datetime.
weekofyear()	Returns an integer representing the week number.

Dynamic/array functions

Function Name	Description
array_concat()	Concatenates a number of dynamic arrays to a single array.
array_iff()	Applies element-wise iif function on arrays.
array_index_of()	Searches the array for the specified item, and returns its position.
array_length()	Calculates the number of elements in a dynamic array.
array_reverse()	Reverses the order of the elements in a dynamic array.
array_rotate_left()	Rotates values inside a dynamic array to the left.
array_rotate_right()	Rotates values inside a dynamic array to the right.
array_shift_left()	Shifts values inside a dynamic array to the left.
array_shift_right()	Shifts values inside a dynamic array to the right.
array_slice()	Extracts a slice of a dynamic array.
array_sort_asc()	Sorts a collection of arrays in ascending order.
array_sort_desc()	Sorts a collection of arrays in descending order.
array_split()	Builds an array of arrays split from the input array.
array_sum()	Calculates the sum of a dynamic array.
bag_has_key()	Checks whether a dynamic bag column contains a given key.
bag_keys()	Enumerates all the root keys in a dynamic property-bag object.
bag_merge()	Merges dynamic property-bags into a dynamic property-bag with all properties merged.
bag_pack()	Creates a dynamic object (property bag) from a list of names and values.
bag_pack_columns()	Creates a dynamic object (property bag) from a list of columns.
bag_remove_keys()	Removes keys and associated values from a dynamic property-bag.
bag_set_key()	Sets a given key to a given value in a dynamic property-bag.
jaccard_index()	Computes the Jaccard index of two sets.
pack_all()	Creates a dynamic object (property bag) from all the columns of the tabular expression.
pack_array()	Packs all input values into a dynamic array.
repeat()	Generates a dynamic array holding a series of equal values.
set_difference()	Returns an array of the set of all distinct values that are in the first array but aren't in other arrays.
set_has_element()	Determines whether the specified array contains the specified element.
set_intersect()	Returns an array of the set of all distinct values that are in all arrays.
set_union()	Returns an array of the set of all distinct values that are in any of provided arrays.
treepath()	Enumerates all the path expressions that identify leaves in a dynamic object.
zip()	The zip function accepts any number of dynamic arrays. Returns an array whose elements are each an array with the elements of the input arrays of the same index.

Window scalar functions

Function Name	Description
next()	For the serialized row set, returns a value of a specified column from the later row according to the offset.
prev()	For the serialized row set, returns a value of a specified column from the earlier row according to the offset.
row_cumsum()	Calculates the cumulative sum of a column.
row_number()	Returns a row's number in the serialized row set - consecutive numbers starting from a given index or from 1 by default.
row_rank_dense()	Returns a row's dense rank in the serialized row set.
row_rank_min()	Returns a row's minimal rank in the serialized row set.

Flow control functions

Function Name	Description
toscalar()	Returns a scalar constant value of the evaluated expression.

Mathematical functions

Function Name	Description
abs()	Calculates the absolute value of the input.
acos()	Returns the angle whose cosine is the specified number (the inverse operation of cos()).
asin()	Returns the angle whose sine is the specified number (the inverse operation of sin()).
atan()	Returns the angle whose tangent is the specified number (the inverse operation of tan()).
atan2()	Calculates the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x).
beta_cdf()	Returns the standard cumulative beta distribution function.
beta_inv()	Returns the inverse of the beta cumulative probability beta density function.
beta_pdf()	Returns the probability density beta function.
cos()	Returns the cosine function.
cot()	Calculates the trigonometric cotangent of the specified angle, in radians.
degrees()	Converts angle value in radians into value in degrees, using formula degrees = (180 / PI) * angle-in-radians.
erf()	Returns the error function.
erfc()	Returns the complementary error function.
exp()	The base-e exponential function of x, which is e raised to the power x: e^x.
exp10()	The base-10 exponential function of x, which is 10 raised to the power x: 10^x.
exp2()	The base-2 exponential function of x, which is 2 raised to the power x: 2^x.
gamma()	Computes gamma function.
isfinite()	Returns whether input is a finite value (isn't infinite or NaN).
isinf()	Returns whether input is an infinite (positive or negative) value.
isnan()	Returns whether input is Not-a-Number (NaN) value.
log()	Returns the natural logarithm function.
log10()	Returns the common (base-10) logarithm function.
log2()	Returns the base-2 logarithm function.
loggamma()	Computes log of absolute value of the gamma function.
not()	Reverses the value of its bool argument.
pi()	Returns the constant value of Pi (π).
pow()	Returns a result of raising to power.
radians()	Converts angle value in degrees into value in radians, using formula radians = (PI / 180) * angle-in-degrees.
rand()	Returns a random number.
range()	Generates a dynamic array holding a series of equally spaced values.
round()	Returns the rounded source to the specified precision.
sign()	Sign of a numeric expression.
sin()	Returns the sine function.
sqrt()	Returns the square root function.
tan()	Returns the tangent function.
welch_test()	Computes the p-value of the Welch-test function.

Metadata functions

Function Name	Description
column_ifexists()	Takes a column name as a string and a default value. Returns a reference to the column if it exists, otherwise - returns the default value.
current_cluster_endpoint()	Returns the current cluster running the query.
current_database()	Returns the name of the database in scope.
current_principal()	Returns the current principal running this query.
current_principal_details()	Returns details of the principal running the query.
current_principal_is_member_of()	Checks group membership or principal identity of the current principal running the query.
cursor_after()	Used to access to the records that were ingested after the previous value of the cursor.
estimate_data_size()	Returns an estimated data size of the selected columns of the tabular expression.
extent_id()	Returns a unique identifier that identifies the data shard ("extent") that the current record resides in.
extent_tags()	Returns a dynamic array with the tags of the data shard ("extent") that the current record resides in.
ingestion_time()	Retrieves the record's $IngestionTime hidden datetime column, or null.

Rounding functions

Function Name	Description
bin()	Rounds values down to an integer multiple of a given bin size.
bin_at()	Rounds values down to a fixed-size "bin", with control over the bin's starting point. (See also bin function.)
ceiling()	Calculates the smallest integer greater than, or equal to, the specified numeric expression.

Conditional functions

Function Name	Description
case()	Evaluates a list of predicates and returns the first result expression whose predicate is satisfied.
coalesce()	Evaluates a list of expressions and returns the first non-null (or non-empty for string) expression.
iff()	Evaluate the first argument (the predicate), and returns the value of either the second or third arguments, depending on whether the predicate evaluated to true (second) or false (third).
max_of()	Returns the maximum value of several evaluated numeric expressions.
min_of()	Returns the minimum value of several evaluated numeric expressions.

Series element-wise functions

Function Name	Description
series_abs()	Calculates the element-wise absolute value of the numeric series input.
series_acos()	Calculates the element-wise arccosine function of the numeric series input.
series_add()	Calculates the element-wise addition of two numeric series inputs.
series_asin()	Calculates the element-wise arcsine function of the numeric series input.
series_atan()	Calculates the element-wise arctangent function of the numeric series input.
series_ceiling()	Calculates the element-wise ceiling function of the numeric series input.
series_cos()	Calculates the element-wise cosine function of the numeric series input.
series_divide()	Calculates the element-wise division of two numeric series inputs.
series_equals()	Calculates the element-wise equals (==) logic operation of two numeric series inputs.
series_exp()	Calculates the element-wise base-e exponential function (e^x) of the numeric series input.
series_floor()	Calculates the element-wise floor function of the numeric series input.
series_greater()	Calculates the element-wise greater (>) logic operation of two numeric series inputs.
series_greater_equals()	Calculates the element-wise greater or equals (>=) logic operation of two numeric series inputs.
series_less()	Calculates the element-wise less (<) logic operation of two numeric series inputs.
series_less_equals()	Calculates the element-wise less or equal (<=) logic operation of two numeric series inputs.
series_log()	Calculates the element-wise natural logarithm function (base-e) of the numeric series input.
series_multiply()	Calculates the element-wise multiplication of two numeric series inputs.
series_not_equals()	Calculates the element-wise not equals (!=) logic operation of two numeric series inputs.
series_pow()	Calculates the element-wise power of two numeric series inputs.
series_sign()	Calculates the element-wise sign of the numeric series input.
series_sin()	Calculates the element-wise sine function of the numeric series input.
series_subtract()	Calculates the element-wise subtraction of two numeric series inputs.
series_tan()	Calculates the element-wise tangent function of the numeric series input.

Series processing functions

Function Name	Description
series_cosine_similarity()	Calculates the cosine similarity of two numeric series.
series_decompose()	Does a decomposition of the series into components.
series_decompose_anomalies()	Finds anomalies in a series based on series decomposition.
series_decompose_forecast()	Forecast based on series decomposition.
series_dot_product()	Calculates the dot product of two numeric series.
series_fill_backward()	Performs backward fill interpolation of missing values in a series.
series_fill_const()	Replaces missing values in a series with a specified constant value.
series_fill_forward()	Performs forward fill interpolation of missing values in a series.
series_fill_linear()	Performs linear interpolation of missing values in a series.
series_fft()	Applies the Fast Fourier Transform (FFT) on a series.
series_fir()	Applies a Finite Impulse Response filter on a series.
series_fit_2lines()	Applies two segments linear regression on a series, returning multiple columns.
series_fit_2lines_dynamic()	Applies two segments linear regression on a series, returning dynamic object.
series_fit_line()	Applies linear regression on a series, returning multiple columns.
series_fit_line_dynamic()	Applies linear regression on a series, returning dynamic object.
series_fit_poly()	Applies polynomial regression on a series, returning multiple columns.
series_ifft()	Applies the Inverse Fast Fourier Transform (IFFT) on a series.
series_iir()	Applies an Infinite Impulse Response filter on a series.
series_magnitude()	Calculates the magnitude of the numeric series.
series_outliers()	Scores anomaly points in a series.
series_pearson_correlation()	Calculates the Pearson correlation coefficient of two series.
series_periods_detect()	Finds the most significant periods that exist in a time series.
series_periods_validate()	Checks whether a time series contains periodic patterns of given lengths.
series_seasonal()	Finds the seasonal component of the series.
series_stats()	Returns statistics for a series in multiple columns.
series_stats_dynamic()	Returns statistics for a series in dynamic object.
series_sum()	Calculates the sum of numeric series elements.

String functions

Function Name	Description
base64_encode_tostring()	Encodes a string as base64 string.
base64_encode_fromguid()	Encodes a GUID as base64 string.
base64_decode_tostring()	Decodes a base64 string to a UTF-8 string.
base64_decode_toarray()	Decodes a base64 string to an array of long values.
base64_decode_toguid()	Decodes a base64 string to a GUID.
countof()	Counts occurrences of a substring in a string. Plain string matches may overlap; regex matches don't.
extract()	Get a match for a regular expression from a text string.
extract_all()	Get all matches for a regular expression from a text string.
extract_json()	Get a specified element out of a JSON text using a path expression.
has_any_index()	Searches the string for items specified in the array and returns the position of the first item found in the string.
indexof()	Function reports the zero-based index of the first occurrence of a specified string within input string.
isempty()	Returns true if the argument is an empty string or is null.
isnotempty()	Returns true if the argument isn't an empty string or a null.
isnotnull()	Returns true if the argument is not null.
isnull()	Evaluates its sole argument and returns a bool value indicating if the argument evaluates to a null value.
parse_command_line()	Parses a Unicode command line string and returns an array of the command line arguments.
parse_csv()	Splits a given string representing comma-separated values and returns a string array with these values.
parse_ipv4()	Converts input to long (signed 64-bit) number representation.
parse_ipv4_mask()	Converts input string and IP-prefix mask to long (signed 64-bit) number representation.
parse_ipv6()	Converts IPv6 or IPv4 string to a canonical IPv6 string representation.
parse_ipv6_mask()	Converts IPv6 or IPv4 string and netmask to a canonical IPv6 string representation.
parse_json()	Interprets a string as a JSON value and returns the value as dynamic.
parse_url()	Parses an absolute URL string and returns a dynamic object contains all parts of the URL.
parse_urlquery()	Parses a url query string and returns a dynamic object contains the Query parameters.
parse_version()	Converts input string representation of version to a comparable decimal number.
replace_regex()	Replace all regex matches with another string.
replace_string()	Replace all single string matches with a specified string.
replace_strings()	Replace all multiple strings matches with specified strings.
punycode_from_string()	Encodes domain name to Punycode form.
punycode_to_string()	Decodes domain name from Punycode form.
reverse()	Function makes reverse of input string.
split()	Splits a given string according to a given delimiter and returns a string array with the contained substrings.
strcat()	Concatenates between 1 and 64 arguments.
strcat_delim()	Concatenates between 2 and 64 arguments, with delimiter, provided as first argument.
strcmp()	Compares two strings.
strlen()	Returns the length, in characters, of the input string.
strrep()	Repeats given string provided number of times (default - 1).
substring()	Extracts a substring from a source string starting from some index to the end of the string.
toupper()	Converts a string to upper case.
translate()	Replaces a set of characters ('searchList') with another set of characters ('replacementList') in a given a string.
trim()	Removes all leading and trailing matches of the specified regular expression.
trim_end()	Removes trailing match of the specified regular expression.
trim_start()	Removes leading match of the specified regular expression.
url_decode()	The function converts encoded URL into a regular URL representation.
url_encode()	The function converts characters of the input URL into a format that can be transmitted over the Internet.

IPv4/IPv6 functions

Function Name	Description
ipv4_compare()	Compares two IPv4 strings.
ipv4_is_in_range()	Checks if IPv4 string address is in IPv4-prefix notation range.
ipv4_is_in_any_range()	Checks if IPv4 string address is any of the IPv4-prefix notation ranges.
ipv4_is_match()	Matches two IPv4 strings.
ipv4_is_private()	Checks if IPv4 string address belongs to a set of private network IPs.
ipv4_netmask_suffix	Returns the value of the IPv4 netmask suffix from IPv4 string address.
parse_ipv4()	Converts input string to long (signed 64-bit) number representation.
parse_ipv4_mask()	Converts input string and IP-prefix mask to long (signed 64-bit) number representation.
ipv4_range_to_cidr_list()	Converts IPv4 address range to a list of CIDR ranges.
ipv6_compare()	Compares two IPv4 or IPv6 strings.
ipv6_is_match()	Matches two IPv4 or IPv6 strings.
parse_ipv6()	Converts IPv6 or IPv4 string to a canonical IPv6 string representation.
parse_ipv6_mask()	Converts IPv6 or IPv4 string and netmask to a canonical IPv6 string representation.
format_ipv4()	Parses input with a netmask and returns string representing IPv4 address.
format_ipv4_mask()	Parses input with a netmask and returns string representing IPv4 address as CIDR notation.
ipv6_is_in_range()	Checks if an IPv6 string address is in IPv6-prefix notation range.
ipv6_is_in_any_range()	Checks if an IPv6 string address is in any of the IPv6-prefix notation ranges.
geo_info_from_ip_address()	Retrieves geolocation information about IPv4 or IPv6 addresses.

IPv4 text match functions

Function Name	Description
has_ipv4()	Searches for an IPv4 address in a text.
has_ipv4_prefix()	Searches for an IPv4 address or prefix in a text.
has_any_ipv4()	Searches for any of the specified IPv4 addresses in a text.
has_any_ipv4_prefix()	Searches for any of the specified IPv4 addresses or prefixes in a text.

Type functions

Function Name	Description
gettype()	Returns the runtime type of its single argument.

Scalar aggregation functions

Function Name	Description
dcount_hll()	Calculates the dcount from hll results (which was generated by hll or hll-merge).
hll_merge()	Merges hll results (scalar version of the aggregate version hll-merge()).
percentile_tdigest()	Calculates the percentile result from tdigest results (which was generated by tdigest or merge_tdigest).
percentile_array_tdigest()	Calculates the percentile array result from tdigest results (which was generated by tdigest or merge_tdigest).
percentrank_tdigest()	Calculates the percentage ranking of a value in a dataset.
rank_tdigest()	Calculates relative rank of a value in a set.
merge_tdigest()	Merge tdigest results (scalar version of the aggregate version tdigest-merge()).

Geospatial functions

Function Name	Description
geo_angle()	Calculates clockwise angle in radians between two lines on Earth.
geo_azimuth()	Calculates clockwise angle in radians between the line from point1 to true north and a line from point1 to point2 on Earth.
geo_distance_2points()	Calculates the shortest distance between two geospatial coordinates on Earth.
geo_distance_point_to_line()	Calculates the shortest distance between a coordinate and a line or multiline on Earth.
geo_distance_point_to_polygon()	Calculates the shortest distance between a coordinate and a polygon or multipolygon on Earth.
geo_intersects_2lines()	Calculates whether the two lines or multilines intersects.
geo_intersects_2polygons()	Calculates whether the two polygons or multipolygons intersects.
geo_intersects_line_with_polygon()	Calculates whether the line or multiline intersects with polygon or multipolygon.
geo_intersection_2lines()	Calculates the intersection of two lines or multilines.
geo_intersection_2polygons()	Calculates the intersection of two polygons or multipolygons.
geo_intersection_line_with_polygon()	Calculates the intersection of line or multiline with polygon or multipolygon.
geo_point_buffer()	Calculates polygon that contains all points within the given radius of the point on Earth.
geo_point_in_circle()	Calculates whether the geospatial coordinates are inside a circle on Earth.
geo_point_in_polygon()	Calculates whether the geospatial coordinates are inside a polygon or a multipolygon on Earth.
geo_point_to_geohash()	Calculates the Geohash string value for a geographic location.
geo_point_to_s2cell()	Calculates the S2 Cell token string value for a geographic location.
geo_point_to_h3cell()	Calculates the H3 Cell token string value for a geographic location.
geo_line_buffer()	Calculates polygon or multipolygon that contains all points within the given radius of the input line or multiline on Earth.
geo_line_centroid()	Calculates the centroid of line or a multiline on Earth.
geo_line_densify()	Converts planar line edges to geodesics by adding intermediate points.
geo_line_length()	Calculates the total length of line or a multiline on Earth.
geo_line_simplify()	Simplifies line or a multiline by replacing nearly straight chains of short edges with a single long edge on Earth.
geo_line_to_s2cells()	Calculates S2 cell tokens that cover a line or multiline on Earth. Useful geospatial join tool.
geo_polygon_area()	Calculates the area of polygon or a multipolygon on Earth.
geo_polygon_buffer()	Calculates polygon or multipolygon that contains all points within the given radius of the input polygon or multipolygon on Earth.
geo_polygon_centroid()	Calculates the centroid of polygon or a multipolygon on Earth.
geo_polygon_densify()	Converts polygon or multipolygon planar edges to geodesics by adding intermediate points.
geo_polygon_perimeter()	Calculates the length of the boundary of polygon or a multipolygon on Earth.
geo_polygon_simplify()	Simplifies polygon or a multipolygon by replacing nearly straight chains of short edges with a single long edge on Earth.
geo_polygon_to_s2cells()	Calculates S2 Cell tokens that cover a polygon or multipolygon on Earth. Useful geospatial join tool.
geo_polygon_to_h3cells()	Converts polygon to H3 cells. Useful geospatial join and visualization tool.
geo_geohash_to_central_point()	Calculates the geospatial coordinates that represent the center of a Geohash rectangular area.
geo_geohash_neighbors()	Calculates the geohash neighbors.
geo_geohash_to_polygon()	Calculates the polygon that represents the geohash rectangular area.
geo_s2cell_to_central_point()	Calculates the geospatial coordinates that represent the center of an S2 Cell.
geo_s2cell_neighbors()	Calculates the S2 cell neighbors.
geo_s2cell_to_polygon()	Calculates the polygon that represents the S2 Cell rectangular area.
geo_h3cell_to_central_point()	Calculates the geospatial coordinates that represent the center of an H3 Cell.
geo_h3cell_neighbors()	Calculates the H3 cell neighbors.
geo_h3cell_to_polygon()	Calculates the polygon that represents the H3 Cell rectangular area.
geo_h3cell_parent()	Calculates the H3 cell parent.
geo_h3cell_children()	Calculates the H3 cell children.
geo_h3cell_level()	Calculates the H3 cell resolution.
geo_h3cell_rings()	Calculates the H3 cell Rings.
geo_simplify_polygons_array()	Simplifies polygons by replacing nearly straight chains of short edges with a single long edge, while ensuring mutual boundaries consistency related to each other, on Earth.
geo_union_lines_array()	Calculates the union of lines or multilines on Earth.
geo_union_polygons_array()	Calculates the union of polygons or multipolygons on Earth.

Hash functions

Function Name	Description
hash()	Returns a hash value for the input value.
hash_combine()	Combines two or more hash values.
hash_many()	Returns a combined hash value of multiple values.
hash_md5()	Returns an MD5 hash value for the input value.
hash_sha1()	Returns a SHA1 hash value for the input value.
hash_sha256()	Returns a SHA256 hash value for the input value.
hash_xxhash64()	Returns an XXHASH64 hash value for the input value.

Units conversion functions

Function Name	Description
convert_angle()	Returns the input value converted from one angle unit to another
convert_energy()	Returns the input value converted from one energy unit to another
convert_force()	Returns the input value converted from one force unit to another
convert_length()	Returns the input value converted from one length unit to another
convert_mass()	Returns the input value converted from one mass unit to another
convert_speed()	Returns the input value converted from one speed unit to another
convert_temperature()	Returns the input value converted from one temperature unit to another
convert_volume()	Returns the input value converted from one volume unit to another