SQL Functions List
Here is an alphabetical list of all the SQL functions SingleStoreDB Cloud currently supports. This list is subject to change.
Function Name | 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 | |
An aggregate function that arbitrarily picks one value from the group. This can include a NULL value if one is present in the group. | Aggregate | |
Returns an estimate of the number of distinct values in a collection. | 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. The calculation occurs for a given percentage. | 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. It is similar to calculating the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result. | 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 datatype. | 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.e., the first pair of bits, the second pair of bits, etc.). | 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. Each place shifted is equivalent to multiplying by 2. | 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. Evaluates the specified expression/condition and returns the result from the matching expression. | 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 datatype. | 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. Sunday is 1, Monday is 2, etc. | 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 | |
Returns the scalar product, or dot product, of two vector input values. | 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 | |
Returns the scalar Euclidean distance between two vector input values. It is a scalar function. | 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. If the first argument is false or NULL, returns third 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 | |
Removes 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 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 | |
Returns true if, in the JSON, there is a value at the specified filter path which evaluates the optional filter predicate as true. | 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 SingleStoreDB Cloud 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_INCREMENT column. | 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 through 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 SingleStoreDB Cloud, percentile functions are available as window functions and aggregate functions. | Window | |
In SingleStoreDB Cloud, 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.0. | 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 | |
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 | |
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 | |
Multiplies each element in the vector blob with the specified scalar value and returns a single vector blob. | 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. This is analogous to the | User-Defined Variables | |
Hashes the given string or binary data using the so-called secure hash algorithm, version 1. SHA1 has been deprecated as a means of securely signing data. | String | |
Hashes the given string or binary data using the secure hash algorithm, version 2. As of this writing (early 2015), SHA2 is considered secure for generating collision-resistant hashes. | 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 SingleStoreDB Cloud (MemSQL) Procedural SQL (MPSQL) 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, 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. This is similar to the TRUNCATE function except that the precision (d) is not required. | Numeric |
Returns the number x truncated to d decimal places. This is similar to the TRUNC function, except that the precision (d) is required. | 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 | |
Adds two input vectors and returns a single vector blob. | Vector | |
Returns the sum of all elements of a vector expression. | Vector | |
Returns the k-th element in a vector expression where k is 0-indexed. | Vector | |
Multiplies two vector blobs and returns a single vector blob. | Vector | |
Return the number of elements in a vector. | Vector | |
Sorts the elements in the vector expression in ascending or descending order. | Vector | |
Returns a vector blob from two input vectors by subtracting the second vector from the first vector. | 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. Monday is 0, Tuesday is 1, etc. | Date and Time | |
Extracts the year from the given datetime. | Date and Time |