SQL Functions List
Here is an alphabetical list of all the SQL functions SingleStore currently supports.
Function |
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 128-bit key or a 256-bit key. |
String |
|
Encrypts the given plaintext using the AES (Advanced Encryption Standard) algorithm with a 128-bit key or a 256-bit 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 |
|
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 non-NULL 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 code-value 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 fixed-length decimal part. |
String |
|
Returns the number of rows when using commands that return a resultset, such as |
Information |
|
Takes a base-64 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 full-text query. |
Full-Text Search |
|
Extracts the hour of the day from the given datetime, zero-indexed. |
Date and Time |
|
If the first argument is true, returns second argument. |
Conditional |
|
The |
Comparison Operators |
|
Converts an IPv4 address to a 32-bit integer. |
String |
|
Converts a 32-bit 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 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 comma-separated argument list to a JSON object. |
JSON |
|
Removes a key/value pair from a JSON map or array. |
JSON |
|
Extracts data from a JSON object or array based on a path specification. |
JSON |
|
Returns the Javascript type of the given JSON object. |
JSON |
|
Returns the top-level keys of a JSON object in the form of a JSON array. |
JSON |
|
Computes the length of a JSON map or array. |
JSON |
|
Checks for the existence of data within a JSON document that matches 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 columnstore tables created with a FULLTEXT index, the text columns in that table can be searched by using the |
Full-Text 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 |
|
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 user-defined variable for use in SQL statements. |
User-Defined Variables |
|
Hashes the given string or binary data using the so-called 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 non-NULL 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 base-64 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, a single row, or a PSQL array or record to a JSON value. |
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 |
|
Adds all vectors in a column and returns a vector which is the sum of those vectors. |
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: July 10, 2025