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 re-run 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 128-bit key . |
String |
|
Encrypts the given plaintext using the AES (Advanced Encryption Standard) algorithm with a 128-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 |
|
|
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 |
|
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 |
|
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 |
|
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 |
|
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, 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 |
|
The |
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: September 18, 2024