SQL Functions List
Here is an alphabetical list of all the SQL functions SingleStore currently supports.
Many of the SQL query examples included in the commands below have links to open the query in the SingleStore live SQL Playground, so you can try the command yourself and rerun it with your changes to see the new results.
JSON 
Function Description 
Grouping 

Returns the positive value of a given number, regardless of sign. 
Numeric 

Returns the arc cosine of x in radians, or NULL if x is not in the range [1, 1]. 
Numeric 

Adds the given time object to a time or datetime object. 
Date and Time 

Decrypts the given ciphertext using the AES (Advanced Encryption Standard) algorithm, with a 128bit key or a 256bit key. 
String 

Encrypts the given plaintext using the AES (Advanced Encryption Standard) algorithm with a 128bit key or a 256bit key. 
String 

Returns the unique ID ( 
Information 

An aggregate function that arbitrarily picks one value from the group. 
Aggregate 

Aggregate function. 
Distinct Count Estimation 

For a collection of values, it creates a new HyperLogLog state and returns a value representing the new state. 
Distinct Count Estimation 

Merges two or more HyperLogLog states into a new state and returns a value of type 
Distinct Count Estimation 

Returns an estimate of the number of distinct values for one or more HyperLogLog states. 
Distinct Count Estimation 

This function is the fast approximate version of 
Geospatial 

Calculates the approximate percentile for a set of values. 
Aggregate 

Returns the ASCII value of the leftmost character of the given string. 
String 

Returns the arc sine of x in radians, or NULL if x is not in the range [1, 1]. 
Numeric 

Returns the arc tangent of x. 
Numeric 

Returns the arc tangent of x and y. 
Numeric 

Calculate the average value from a set of numbers. 
Aggregate 


Comparison Operators 

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. 
String 

Casts the input to a binary data type. 
String 

Performs an AND operation on each bit between expressions. 
Aggregate 

Given a number, return the number of ones in its binary representation. 
Numeric 

Performs an OR operation on each bit between expressions. 
Aggregate 

Performs an XOR function on corresponding pairs of bits (i. 
Aggregate 

Combines two integers to produce a third integer whose binary bits are 1 if and only if both bits in the input numbers are also 1. 
Numeric 

Moves all of the bits of num1 to the left by num2 places. 
Numeric 

Inverts all of the bits of a given number. 
Numeric 

Combines two integers to produce a third integer whose binary bits are 1 if either bit in the input numbers are also 1. 
Numeric 

Moves all of the bits of num1 to the right by num2 places. 
Numeric 

Combines two integers to produce a third integer whose binary bits are 1 if either bit in the input numbers are different, ie a 1 in the first number and a 0 in the second, or vice versa. 
Numeric 

This function is used to employ BM25 partitionscoped scoring for 
FullText Search 

Returns a SingleStore 
BSON 

Adds the specified BSON value to the end of the input BSON array, and returns the array with the appended value as the result. 
BSON 

Slices the input array and returns a subset. 
BSON 

Creates a BSON array from the input values. 
BSON 

Creates a BSON object from the specified <key, value> pairs. 
BSON 

Compares two BSON values of similar types. 
BSON 

Extracts the value for the specified key from the BSON data, converts it to the SingleStore 
BSON 

Extracts the boolean value for the specified key from the BSON data, converts it to the SingleStore 
BSON 

Extracts and returns a BSON value from the BSON object, optionally at the specified path. 
BSON 

Extracts the value for the specified key from the BSON data, converts it to a SingleStore 
BSON 

Extracts the numeric value for the specified key from the BSON data, converts it to the SingleStore 
BSON 

Extracts the string value for the specified key from the BSON data, converts it to a SingleStore string type, and returns the value. 
BSON 

Returns a string that specifies the BSON type of the input BSON value. 
BSON 

Applies a mask to a BSON document and returns the subset of the original document. 
BSON 

Returns the number of keys present in the BSON object or array. 
BSON 


BSON 

Merges two BSON objects. 
BSON 

This is a set of four BSON functions that normalize BSON documents. 
BSON 

Sets (or overwrites) the value at the specified key in a BSON object or array and returns the BSON object or array. 
BSON 

Converts a BSON document to a PSQL array. 
BSON 

Switch statement construct. 
Conditional 

Rounds a number up to the next highest integer. 
Numeric 

Processes each argument 
String 

Returns the Unicode character length of the given string. 
String 

Takes a string argument and returns the character set of that argument. 
String 

Given a list of values, returns the first nonNULL value. 
Conditional 

Concatenates all of the values in its argument list. 
String 


String 

Returns the unique connection ID of the current connection. 
Information 

Converts numbers between different number bases. 
Numeric 

Casts the input to the given data type. 
Conditional 

Converts a given datetime object from one timezone to another. 
Date and Time 

Computes the cosine of x, where x is given in radians. 
Numeric 

Returns the cotangent of x, where x is given in radians, or NULL if x is invalid. 
Numeric 

Counts the number of rows passed in. 
Aggregate 

Hashes the given string or binary data using the CRC32C (Castagnoli CRC32) algorithm. 
String 

Returns the current date. 
Date and Time 

Returns the current time of day. 
Date and Time 

Returns the current date and time. 
Date and Time 

Returns the name of the currently selected database. 
Information 

Extracts the date part from a datetime object. 
Date and Time 

Adds the given interval of time to a date or datetime object. 
Date and Time 

Convert the input datetime object to a string according to the specified format. 
Date and Time 

Subtracts the given interval of time to a date or datetime object. 
Date and Time 

Truncates a timestamp using the specified date part. 
Date and Time 

Subtracts to find the number of days between two dates. 
Date and Time 

Extracts the day of the month from the given datetime. 
Date and Time 

Extracts the name of the day of the week from a date. 
Date and Time 

Extracts the day of the week from a date. 
Date and Time 

Extracts the day of the year from a date. 
Date and Time 

Searches for a code in a codevalue list and returns the corresponding value. 
Conditional 

Converts x from radians to degrees. 
Numeric 

Returns the rank of the current row within the partition as specified by the order by clause. 
Window 

The 
Vector 

Returns the Nth value in its list of arguments. 
Conditional 

Returns an estimate of the amount of memory, in megabytes, required to run a query. 
Information 

Returns an estimate of the elapsed time, in seconds, required to run a query. 
Information 

The 
Vector 

Returns the result of e raised to the power of the argument specified. 
Numeric 

Extracts specified components from a given datetime. 
Date and Time 

Returns the first index of the 
String 

A function that returns the first value of a set of input values, defined as the value associated with the minimum time. 
Time Series 

Returns an expression evaluated at the first row of the window frame. 
Window 

Rounds a number down to the next lowest integer. 
Numeric 

Rounds and formats a number with thousands separators and a fixedlength decimal part. 
String 

Returns the number of rows when using commands that return a resultset, such as 
Information 

Takes a base64 encoded string and returns the decoded result as a binary string. 
String 

Returns the date object corresponding to the input day number. 
Date and Time 

Given an integer Unix timestamp, return the corresponding datetime. 
Date and Time 

This function measures the square meter area of a given polygon. 
Geospatial 

This function takes in two geospatial objects and determines whether the first one completely covers the area of the second. 
Geospatial 

This function takes in two geospatial objects and measures the shortest distance between them, in meters, using the standard metric for distance on a sphere. 
Geospatial 

This function takes in two geospatial objects and determines whether any part of them overlap. 
Geospatial 

This function extracts the latitude portion of a 
Geospatial 

This function measures the length of a path or the total perimeter of a polygon, in meters. 
Geospatial 

This function extracts the longitude portion of a 
Geospatial 

This function takes two floats or doubles and returns a 
Geospatial 

This function takes in two geospatial objects and determines whether any part of them is within the given distance, in meters. 
Geospatial 

Return a format string for the specified datetime type and standard. 
Date and Time 

Returns the highest value observed in its arguments. 
Conditional 

Concatenates all of the values passed in during aggregation. 
Aggregate 

Returns the hexadecimal representation of numeric, string, or binary data. 
String 

Returns the fragment of text near the matching words from a fulltext query. 
FullText Search 

Extracts the hour of the day from the given datetime, zeroindexed. 
Date and Time 

If the first argument is true, returns second argument. 
Conditional 

The 
Comparison Operators 

Converts an IPv4 address to a 32bit integer. 
String 

Converts a 32bit integer to an IPv4 address. 
String 

Converts an IPv6 or IPv4 internet address from its text representation to its binary representation. 
String 

Converts an IPv6 or IPv4 internet address from its binary representation to its text representation. 
String 

Converts the first letter of each word in a string to upper case. 
String 

Returns the first position of the given search string. 
String 

Returns a 
BSON 

Returns true if the value is NULL. 
Conditional 

Determines whether the provided expression is a valid numeric type. 
Conditional 


JSON 

This is a set of three JSON functions, which return true if the given value is contained in the JSON array. 
JSON 

Converts a JSON array of zero or more floating point numbers to an encoded blob. 
Vector 

This is a set of three JSON functions, which add the given element to the end of a JSON array. 
JSON 

Converts an encoded blob representing a vector to a JSON array representing the same vector. 
Vector 

Converts a commaseparated argument list to a JSON object. 
JSON 

Removes a key/value pair from a JSON map or array. 
JSON 

Extracts data from a JSON document determined by what parts of the path arguments that match. 
JSON 

Returns the Javascript type of the given JSON object. 
JSON 

Returns the toplevel keys of a JSON object in the form of a JSON array. 
JSON 

Computes the length of a JSON map or array. 
JSON 

This function can find data within a JSON document that matches the specified filter conditions. 
JSON 

Also referred as "pretty printing", this function outputs the given JSON object or JSON array in an indented and legible format. 
JSON 

There is a group of three JSON functions, which sets the value of a given key in a JSON map or array. 
JSON 

This is a set of three JSON functions, which deletes the given element indexes from an array, and optionally splices the new values in. 
JSON 

Converts a JSON array to a SingleStore array. 
JSON 

Returns an expression evaluated at some offset before the current row. 
Window 

A function that returns the last value of a set of input values, defined as the value associated with the maximum time. 
Time Series 

Finds the month of the input date, and then returns a date object containing the last day of that month. 
Date and Time 

Returns the last value inserted into an AUTO_ 
Information 

Returns an expression evaluated at the last row of the window frame. 
Window 

Converts the given string to lower case. 
String 

Returns an expression evaluated at some offset after the current row. 
Window 

Returns the lowest value observed in its arguments. 
Conditional 

Extracts the specified number of characters from the left end of a string 
String 

Returns the byte length of a given string, array, or binary object. 
String 

The 
Comparison Operators 

Returns the logarithm of the given argument to base e. 
Numeric 

Returns the current time, in the local time zone. 
Date and Time 

Returns the first position of the given search string. 
String 

Returns the logarithm of the given argument in the given base. 
Numeric 

Returns the logarithm of the given argument to base 10. 
Numeric 

Returns the logarithm of the given argument to base 2. 
Numeric 

Pads the beginning of a string up to the specified character length. 
String 

Removes leading space characters from the given string. 
String 

For tables created with a FULLTEXT index, the columnstore columns in that index can be searched by using the 
FullText Search 

Returns the highest value observed in all rows in an aggregation. 
Aggregate 

Hashes the given string or binary data using the message digest algorithm, version 5. 
String 

It is an aggregate function that returns the median of an expression (middle value in a sorted data set), which separates the higher half in a distribution from the lower half. 
Aggregate 

Extracts the microseconds (fractional seconds part) from the given datetime. 
Date and Time 

Returns the lowest value observed in all rows in an aggregation. 
Aggregate 

Extracts the minutes of the hour from the given datetime. 
Date and Time 


Aggregate 

Extracts the month from the given datetime. 
Date and Time 

Extracts the name of the month from the given datetime. 
Date and Time 

Subtracts to find the number of months between two dates. 
Date and Time 

Disables the parameterization of constants in a query before a query plan is compiled. 
Code Generation 

Returns the current date and time in the configured time zone. 
Date and Time 

Returns an expression evaluated at the nth row of the window frame. 
Window 

Divides the input into the specified number of buckets and returns the bucket number of the current row. 
Window 

Returns NULL if the arguments are equal, otherwise the first argument. 
Conditional 

Checks if an expression evaluates to 
Conditional 

Returns the percent rank of the current row within the partition as specified by the order by clause. 
Window 

In SingleStore, percentile functions are available as window functions and aggregate functions. 
Window 

In SingleStore, percentile functions are available as window functions and aggregate functions. 
Window 

Returns the value of pi to 7 decimal places. 
Numeric 

Returns the given number exponentiated to the given power. 
Numeric 

Extracts the quarter of the year from the given datetime, starting with 1. 
Date and Time 

Returns a string enclosed in single quotes. 
String 

Converts x from degrees to radians. 
Numeric 

Returns a random float or double between 0 and 1. 
Numeric 

Returns the rank of the current row within the partition as specified by the order by clause. 
Window 


JSON 

Searches a string for a regular expression pattern and returns an integer indicating the beginning position or ending position of the matched substring. 
Regular Expression 

The 
Regular Expression 

Searches a string for the first occurrence of a regular expression pattern and replaces that occurrence with a replacement string. 
Regular Expression 

Searches a string for a regular expression pattern and returns the matching substring. 
Regular Expression 

Replaces all occurrences of a substring by another string. 
String 

Reverses the character order of a string or returns a 
String 

Extracts the specified number of characters from the right end of a string 
String 


Regular Expression 

Rounds a number to the given precision. 
Numeric 

Returns the number of rows inserted, updated, or deleted by the preceding statement. 
Information 

Returns the number of the current row within its partition. 
Window 

Pads the end of a string up to the specified character length. 
String 

Removes trailing space characters from the given string. 
String 

The 
Vector 

Return a time object corresponding to the input second count. 
Date and Time 

Extracts the seconds of the minute from the given datetime. 
Date and Time 

Provides the ability to hide credentials from queries. 
String 

Sets a value for a userdefined variable for use in SQL statements. 
UserDefined Variables 

Hashes the given string or binary data using the socalled secure hash algorithm, version 1. 
String 

Hashes the given string or binary data using the secure hash algorithm, version 2. 
String 

Returns the sigmoid function of x. 
Numeric 

This function returns 1 if the argument is positive, 1 if it is negative, and 0 if it is 0. 
Numeric 

Computes the sine of x, where x is given in radians. 
Numeric 

Halts execution of the thread for the given number of seconds. 
Conditional 

Splits a string by the specified separator into an array of substrings and then returns the array. 
String 

Returns the square root of a given number, accurate to within the representation of doubles. 
Numeric 

Calculates the population standard deviation value from a set of numbers. 
Aggregate 

Returns the standard deviation of all the values in a specified expression. 
Aggregate 

Returns the population standard deviation for nonNULL values. 
Aggregate 

Calculates the sample standard deviation value from a set of numbers. 
Aggregate 

Extract datetime value from input string according to a specified format. 
Date and Time 

The 
Comparison Operators 

Converts a string to a SingleStore Procedural SQL (PSQL) array of integers (TINYINT UNSIGNED) where each array element is the integer byte value of a character, or a byte within a multiple byte character, in the input string. 
String 

Extracts a range of characters from a string. 
String 

Extracts the portion of a string up to the given number of occurrences of a delimiter. 
String 

Calculate the sum of a set of numbers. 
Aggregate 

Generates and returns a new globally unique identifier (GUID) each time it is called. 
Identifier Generation 

Returns the tangent of x, where x is given in radians. 
Numeric 

Extracts the time part from a datetime object. 
Date and Time 

A function that normalizes time to the nearest bucket start time. 
Time Series 

Convert the input time object to a string according to the specified format. 
Date and Time 

Returns the number of seconds from midnight. 
Date and Time 

Subtracts to find the amount of time between two datetimes or times. 
Date and Time 

Convert the input argument to a datetime object. 
Date and Time 

Adds the given interval of time to a date or datetime object 
Date and Time 

Find between two date or datetime objects the time difference in the specified unit. 
Date and Time 

Returns the argument str converted to base64 encoding. 
String 

Converts a 
Date and Time 

Converts a string to a 
Date and Time 

Returns the day number of the input date object. 
Date and Time 

Converts a table column, an entire table, a scalar value, or a single row to a JSON object. 
JSON 

Converts a 
Numeric 

Returns the number of seconds counting from year 0 to the input datetime. 
Date and Time 

Converts a string to a 
Date and Time 

Removes padding from the ends of the given string. 
String 

TRUNC  Date function 
Truncates a date using the specified format. 
Date and Time 
TRUNC  Numeric function 
Returns the number x truncated to d decimal places. 
Numeric 
Returns the number x truncated to d decimal places. 
Numeric 

Converts the given string to uppercase. 
String 

Converts a hexadecimal representation to the binary equivalent. 
String 

Returns the current Unix time as an integer, or converts the given date into unixtime. 
Date and Time 

Returns the name of the current user. 
Information 

Returns the current date, in the UTC time zone. 
Date and Time 

Returns the current time, in the UTC time zone. 
Date and Time 

Returns the current time, in the UTC time zone. 
Date and Time 

Calculates the population variance value from a set of numbers. 
Aggregate 

Calculates the sample variance value from a set of numbers. 
Aggregate 

The 
Vector 

The 
Vector 


Vector 

The 
Vector 

The 
Vector 

The 
Vector 

The 
Vector 

Derives a vector expression from another vector expression. 
Vector 

Returns a vector, which is a sum of all vectors in a column. 
Vector 

There are eight ways to number weeks, described below. 
Date and Time 

Extracts the day of the week from a date. 
Date and Time 

Extracts the year from the given datetime. 
Date and Time 
Last modified: June 11, 2024