SQL Functions List

Here is an alphabetical list of all the SQL functions SingleStore currently supports. This list is subject to change.

Function	Function Description	Grouping
ABS	Returns the positive value of a given number, regardless of sign.	Numeric
ACOS	Returns the arc cosine of x in radians, or NULL if x is not in the range [-1, 1].	Numeric
ADDTIME	Adds the given time object to a time or datetime object.	Date and Time
AES_DECRYPT	Decrypts the given ciphertext using the AES (Advanced Encryption Standard) algorithm, with a 128-bit key .	String
AES_ENCRYPT	Encrypts the given plaintext using the AES (Advanced Encryption Standard) algorithm with a 128-bit key .	String
AGGREGATOR_ID	Returns the unique ID (`node_id`) for the current aggregator.	Information
ANY_VALUE	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
APPROX_COUNT_DISTINCT	Aggregate function. Returns an estimate of the number of distinct values in a collection.	Distinct Count Estimation
APPROX_COUNT_DISTINCT_ACCUMULATE	For a collection of values, it creates a new HyperLogLog state and returns a value representing the new state.	Distinct Count Estimation
APPROX_COUNT_DISTINCT_COMBINE	Merges two or more HyperLogLog states into a new state and returns a value of type `VARBINARY(16384)` representing the new state.	Distinct Count Estimation
APPROX_COUNT_DISTINCT_ESTIMATE	Returns an estimate of the number of distinct values for one or more HyperLogLog states.	Distinct Count Estimation
APPROX_GEOGRAPHY_INTERSECTS	This function is the fast approximate version of `GEOGRAPHY_INTERSECTS`. It takes in two geospatial objects and determines whether any part of them overlap.	Geospatial
APPROX_PERCENTILE	Calculates the approximate percentile for a set of values. The calculation occurs for a given percentage.	Aggregate
ASCII	Returns the ASCII value of the leftmost character of the given string.	String
ASIN	Returns the arc sine of x in radians, or NULL if x is not in the range [-1, 1].	Numeric
ATAN	Returns the arc tangent of x.	Numeric
ATAN2, ATAN	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
AVG	Calculate the average value from a set of numbers.	Aggregate
BETWEEN, NOT BETWEEN	`BETWEEN` and `NOT BETWEEN` check if values are in or not in a range that includes the two endpoints (inclusive). For example:	Comparison Operators
BIN	Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number.	String
BINARY	Casts the input to a binary data type.	String
BIT_AND	Performs an AND operation on each bit between expressions.	Aggregate
BIT_COUNT	Given a number, return the number of ones in its binary representation.	Numeric
BIT_OR	Performs an OR operation on each bit between expressions.	Aggregate
BIT_XOR	Performs an XOR function on corresponding pairs of bits (i.e., the first pair of bits, the second pair of bits, etc.).	Aggregate
bitwise AND (&)	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
bitwise left shift (<<)	Moves all of the bits of num1 to the left by num2 places. Each place shifted is equivalent to multiplying by 2.	Numeric
bitwise NOT (~)	Inverts all of the bits of a given number.	Numeric
bitwise OR (\|)	Combines two integers to produce a third integer whose binary bits are 1 if either bit in the input numbers are also 1.	Numeric
bitwise right shift (>>)	Moves all of the bits of num1 to the right by num2 places.	Numeric
bitwise XOR (^)	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
CASE	Switch statement construct. Evaluates the specified expression/condition and returns the result from the matching expression.	Conditional
CEIL	Rounds a number up to the next highest integer.	Numeric
CHAR	Processes each argument `N` as an integer and then returns a string consisting of the characters representing the code values of those integers.	String
CHARACTER_LENGTH	Returns the Unicode character length of the given string.	String
CHARSET	Takes a string argument and returns the character set of that argument.	String
COALESCE	Given a list of values, returns the first non-NULL value.	Conditional
CONCAT	Concatenates all of the values in its argument list.	String
CONCAT_WS	`CONCAT_WS` stands for concatenation with separator.	String
CONNECTION_ID	Returns the unique connection ID of the current connection.	Information
CONV	Converts numbers between different number bases.	Numeric
CAST or CONVERT	Casts the input to the given data type.	Conditional
CONVERT_TZ	Converts a given datetime object from one timezone to another.	Date and Time
COS	Computes the cosine of x, where x is given in radians.	Numeric
COT	Returns the cotangent of x, where x is given in radians, or NULL if x is invalid.	Numeric
COUNT	Counts the number of rows passed in.	Aggregate
CRC32	Hashes the given string or binary data using the CRC32C (Castagnoli CRC32) algorithm.	String
CURRENT_DATE And CURDATE	Returns the current date.	Date and Time
CURRENT_TIME And CURTIME	Returns the current time of day.	Date and Time
CURRENT_TIMESTAMP	Returns the current date and time. `NOW()` return a second precision result and `NOW(6)` returns a microsecond precision result.	Date and Time
DATABASE	Returns the name of the currently selected database.	Information
DATE	Extracts the date part from a datetime object.	Date and Time
DATE_ADD	Adds the given interval of time to a date or datetime object.	Date and Time
DATE_FORMAT	Convert the input datetime object to a string according to the specified format.	Date and Time
DATE_SUB	Subtracts the given interval of time to a date or datetime object.	Date and Time
DATE_TRUNC	Truncates a timestamp using the specified date part.	Date and Time
DATEDIFF	Subtracts to find the number of days between two dates.	Date and Time
DAY	Extracts the day of the month from the given datetime.	Date and Time
DAYNAME	Extracts the name of the day of the week from a date.	Date and Time
DAYOFWEEK	Extracts the day of the week from a date. Sunday is 1, Monday is 2, etc.	Date and Time
DAYOFYEAR	Extracts the day of the year from a date.	Date and Time
DECODE	Searches for a code in a code-value list and returns the corresponding value.	Conditional
DEGREES	Converts x from radians to degrees.	Numeric
DENSE_RANK	Returns the rank of the current row within the partition as specified by the order by clause.	Window
DOT_PRODUCT	The `DOT_PRODUCT` function returns the scalar product, or dot product, of two vectors. `DOT_PRODUCT` takes as input two vectors and returns a numeric value.	Vector
ELT	Returns the Nth value in its list of arguments.	Conditional
ESTIMATED_QUERY_LEAF_MEMORY	Returns an estimate of the amount of memory, in megabytes, required to run a query.	Information
ESTIMATED_QUERY_RUNTIME	Returns an estimate of the elapsed time, in seconds, required to run a query.	Information
EUCLIDEAN_DISTANCE	The `EUCLIDEAN_DISTANCE` function returns the euclidean distance between two vector values. `EUCLIDEAN_DISTANCE` takes as input two vectors and returns a numeric value.	Vector
EXP	Returns the result of e raised to the power of the argument specified.	Numeric
EXTRACT	Extracts specified components from a given datetime.	Date and Time
FIELD	Returns the first index of the `str` argument in the `str1`, `str2`, … list, or 0 if it doesn’t exist.	String
FIRST	A function that returns the first value of a set of input values, defined as the value associated with the minimum time.	Time Series
FIRST_VALUE	Returns an expression evaluated at the first row of the window frame.	Window
FLOOR	Rounds a number down to the next lowest integer.	Numeric
FORMAT	Rounds and formats a number with thousands separators and a fixed-length decimal part.	String
FOUND_ROWS	Returns the number of rows when using commands that return a resultset, such as `SELECT`, `DESC`, and `SHOW`.	Information
FROM_BASE64	Takes a base-64 encoded string and returns the decoded result as a binary string.	String
FROM_DAYS	Returns the date object corresponding to the input day number.	Date and Time
FROM_UNIXTIME	Given an integer Unix timestamp, return the corresponding datetime.	Date and Time
GEOGRAPHY_AREA	This function measures the square meter area of a given polygon.	Geospatial
GEOGRAPHY_CONTAINS	This function takes in two geospatial objects and determines whether the first one completely covers the area of the second.	Geospatial
GEOGRAPHY_DISTANCE	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
GEOGRAPHY_INTERSECTS	This function takes in two geospatial objects and determines whether any part of them overlap.	Geospatial
GEOGRAPHY_LATITUDE	This function extracts the latitude portion of a `GeographyPoint`.	Geospatial
GEOGRAPHY_LENGTH	This function measures the length of a path or the total perimeter of a polygon, in meters.	Geospatial
GEOGRAPHY_LONGITUDE	This function extracts the longitude portion of a `GeographyPoint`.	Geospatial
GEOGRAPHY_POINT	This function takes two floats or doubles and returns a `GeographyPoint` type.	Geospatial
GEOGRAPHY_WITHIN_DISTANCE	This function takes in two geospatial objects and determines whether any part of them is within the given distance, in meters.	Geospatial
GET_FORMAT	Return a format string for the specified datetime type and standard.	Date and Time
GREATEST	Returns the highest value observed in its arguments.	Conditional
GROUP_CONCAT	Concatenates all of the values passed in during aggregation.	Aggregate
HEX	Returns the hexadecimal representation of numeric, string, or binary data.	String
HIGHLIGHT	Returns the fragment of text near the matching words from a full-text query.	Full-Text Search
HOUR	Extracts the hour of the day from the given datetime, zero-indexed.	Date and Time
IF	If the first argument is true, returns second argument. If the first argument is false or NULL, returns third argument.	Conditional
IN(), NOT IN()	The `IN()` and `NOT IN()` functions filter the results after checking if the values are in or not in the list of parameters specified.	Comparison Operators
INET_ATON	Converts an IPv4 address to a 32-bit integer.	String
INET_NTOA	Converts a 32-bit integer to an IPv4 address.	String
INET6_ATON	Converts an IPv6 or IPv4 internet address from its text representation to its binary representation.	String
INET6_NTOA	Converts an IPv6 or IPv4 internet address from its binary representation to its text representation.	String
INITCAP	Converts the first letter of each word in a string to upper case.	String
INSTR	Returns the first position of the given search string.	String
ISNULL	Returns true if the value is NULL.	Conditional
JSON_AGG	`JSON_AGG` combines values and returns the combined data as a single JSON row.	JSON
JSON_ARRAY_CONTAINS_<type>	This is a set of three JSON functions, which return true if the given value is contained in the JSON array.	JSON
JSON_ARRAY_PACK	Converts a JSON array of zero or more floating point numbers to an encoded blob.	Vector
JSON_ARRAY_PUSH_<type>	This is a set of three JSON functions, which add the given element to the end of a JSON array.	JSON
JSON_DELETE_KEY	Removes a key/value pair from a JSON map or array.	JSON
JSON_EXTRACT_<type>	Extracts data from a JSON object or array based on a path specification. Will return NULL if the path is not matched or if any of the arguments are NULL.	JSON
JSON_GET_TYPE	Returns the Javascript type of the given JSON object.	JSON
JSON_LENGTH	Computes the length of a JSON map or array.	JSON
JSON_SET_<type>	There is a group of three JSON functions, which sets the value of a given key in a JSON map or array.	JSON
JSON_SPLICE_<type>	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
JSON_TO_ARRAY	Converts a JSON array to a SingleStore array.	JSON
LAG	Returns an expression evaluated at some offset before the current row.	Window
LAST	A function that returns the last value of a set of input values, defined as the value associated with the maximum time.	Time Series
LAST_DAY	Finds the month of the input date, and then returns a date object containing the last day of that month.	Date and Time
LAST_INSERT_ID	Returns the last value inserted into an AUTO_INCREMENT column.	Information
LAST_VALUE	Returns an expression evaluated at the last row of the window frame.	Window
LCASE	Converts the given string to lower case.	String
LEAD	Returns an expression evaluated at some offset after the current row.	Window
LEAST	Returns the lowest value observed in its arguments.	Conditional
LEFT	Extracts the specified number of characters from the left end of a string	String
LENGTH	Returns the byte length of a given string, array, or binary object.	String
LIKE, NOT LIKE	The `LIKE` and `NOT LIKE` operators are useful for matching patterns containing simple wildcards.	Comparison Operators
LN	Returns the logarithm of the given argument to base e.	Numeric
LOCALTIMESTAMP	Returns the current time, in the local time zone.	Date and Time
LOCATE	Returns the first position of the given search string.	String
LOG	Returns the logarithm of the given argument in the given base.	Numeric
LOG10	Returns the logarithm of the given argument to base 10.	Numeric
LOG2	Returns the logarithm of the given argument to base 2.	Numeric
LPAD	Pads the beginning of a string up to the specified character length.	String
LTRIM	Removes leading space characters from the given string.	String
MATCH	For columnstore tables created with a FULLTEXT index, the text columns in that table can be searched by using the `MATCH AGAINST` syntax.	Full-Text Search
MAX	Returns the highest value observed in all rows in an aggregation.	Aggregate
MD5	Hashes the given string or binary data using the message digest algorithm, version 5.	String
MEDIAN	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
MICROSECOND	Extracts the microseconds (fractional seconds part) from the given datetime.	Date and Time
MIN	Returns the lowest value observed in all rows in an aggregation.	Aggregate
MINUTE	Extracts the minutes of the hour from the given datetime.	Date and Time
MOD	`Mod` stands for Modulo. Calculates the remainder of a number divided by another number.	Aggregate
MONTH	Extracts the month from the given datetime.	Date and Time
MONTHNAME	Extracts the name of the month from the given datetime.	Date and Time
MONTHS_BETWEEN	Subtracts to find the number of months between two dates.	Date and Time
NOPARAM	Disables the parameterization of constants in a query before a query plan is compiled.	Code Generation
NOW	Returns the current date and time in the configured time zone.	Date and Time
NTH_VALUE	Returns an expression evaluated at the nth row of the window frame.	Window
NTILE	Divides the input into the specified number of buckets and returns the bucket number of the current row.	Window
NULLIF	Returns NULL if the arguments are equal, otherwise the first argument.	Conditional
NVL and IFNULL	Checks if an expression evaluates to `NULL`, and if so, evaluates a replacement expression and returns its value.	Conditional
PERCENT_RANK	Returns the percent rank of the current row within the partition as specified by the order by clause.	Window
PERCENTILE_CONT and MEDIAN	In SingleStore, percentile functions are available as window functions and aggregate functions.	Window
PERCENTILE_DISC	In SingleStore, percentile functions are available as window functions and aggregate functions.	Window
PI	Returns the value of pi to 7 decimal places.	Numeric
POW	Returns the given number exponentiated to the given power.	Numeric
QUARTER	Extracts the quarter of the year from the given datetime, starting with 1.	Date and Time
QUOTE	Returns a string enclosed in single quotes.	String
RADIANS	Converts x from degrees to radians.	Numeric
RAND	Returns a random float or double between 0 and 1.0.	Numeric
RANK	Returns the rank of the current row within the partition as specified by the order by clause.	Window
REGEXP_INSTR	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
REGEXP_REPLACE	Searches a string for the first occurrence of a regular expression pattern and replaces that occurrence with a replacement string.	Regular Expression
REGEXP_SUBSTR	Searches a string for a regular expression pattern and returns the matching substring.	Regular Expression
REPLACE	Replaces all occurrences of a substring by another string.	String
RIGHT	Extracts the specified number of characters from the right end of a string	String
RLIKE and REGEXP	`RLIKE` and `REGEXP` are synonyms. `RLIKE` is discussed below. The discussion also applies to `REGEXP`, as both functions work identically.	Regular Expression
ROUND	Rounds a number to the given precision.	Numeric
ROW_COUNT	Returns the number of rows inserted, updated, or deleted by the preceding statement.	Information
ROW_NUMBER	Returns the number of the current row within its partition.	Window
RPAD	Pads the end of a string up to the specified character length.	String
RTRIM	Removes trailing space characters from the given string.	String
SEC_TO_TIME	Return a time object corresponding to the input second count.	Date and Time
SECOND	Extracts the seconds of the minute from the given datetime.	Date and Time
SHA1	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
SHA2	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
SIGMOID	Returns the sigmoid function of x.	Numeric
SIGN	This function returns 1 if the argument is positive, -1 if it is negative, and 0 if it is 0.	Numeric
SIN	Computes the sine of x, where x is given in radians.	Numeric
SLEEP	Halts execution of the thread for the given number of seconds.	Conditional
SPLIT	Splits a string by the specified separator into an array of substrings and then returns the array.	String
SQRT	Returns the square root of a given number, accurate to within the representation of doubles.	Numeric
STD	Calculates the population standard deviation value from a set of numbers.	Aggregate
STDDEV	Returns the standard deviation of all the values in a specified expression.	Aggregate
STDDEV_POP	Returns the population standard deviation for non-NULL values.	Aggregate
STDDEV_SAMP	Calculates the sample standard deviation value from a set of numbers.	Aggregate
STR_TO_DATE	Extract datetime value from input string according to a specified format.	Date and Time
strcmp()	The `strcmp(v1, v2)` function takes two string values. It returns 0 if they are equal, -1 if v1 is less than v2, and 1 if v2 is greater than v1.	Comparison Operators
STRING_BYTES	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
SUBSTRING	Extracts a range of characters from a string.	String
SUBSTRING_INDEX	Extracts the portion of a string up to the given number of occurrences of a delimiter.	String
SUM	Calculate the sum of a set of numbers.	Aggregate
TAN	Returns the tangent of x, where x is given in radians.	Numeric
TIME	Extracts the time part from a datetime object.	Date and Time
TIME_BUCKET	A function that normalizes time to the nearest bucket start time.	Time Series
TIME_FORMAT	Convert the input time object to a string according to the specified format.	Date and Time
TIME_TO_SEC	Returns the number of seconds from midnight.	Date and Time
TIMEDIFF	Subtracts to find the amount of time between two datetimes or times.	Date and Time
TIMESTAMP	Convert the input argument to a datetime object.	Date and Time
TIMESTAMPADD	Adds the given interval of time to a date or datetime object	Date and Time
TIMESTAMPDIFF	Find between two date or datetime objects the time difference in the specified unit.	Date and Time
TO_BASE64	Returns the argument str converted to base-64 encoding.	String
TO_CHAR	Converts a `DATE`, `DATETIME`, `DATETIME(6)`, `TIME`, `TIMESTAMP` or `TIMESTAMP(6)` value to a string.	Date and Time
TO_DATE	Converts a string to a `DATE` or `DATETIME` value.	Date and Time
TO_DAYS	Returns the day number of the input date object.	Date and Time
TO_JSON	Converts a table column, an entire table, a scalar value, a single row, or a PSQL array or record to a JSON value.	JSON
TO_NUMBER	Converts a `CHAR` or `VARCHAR` type value to a `DECIMAL` value.	Numeric
TO_SECONDS	Returns the number of seconds counting from year 0 to the input datetime.	Date and Time
TO_TIMESTAMP	Converts a string to a `TIMESTAMP(6)` value.	Date and Time
TRIM	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
TRUNCATE	Returns the number x truncated to d decimal places. This is similar to the TRUNC function, except that the precision (d) is required.	Numeric
UCASE	Converts the given string to uppercase.	String
UNHEX	Converts a hexadecimal representation to the binary equivalent.	String
UNIX_TIMESTAMP	Returns the current Unix time as an integer, or converts the given date into unixtime.	Date and Time
USER	Returns the name of the current user.	Information
UTC_DATE	Returns the current date, in the UTC time zone.	Date and Time
UTC_TIME	Returns the current time, in the UTC time zone.	Date and Time
UTC_TIMESTAMP	Returns the current time, in the UTC time zone.	Date and Time
VARIANCE	Calculates the population variance value from a set of numbers.	Aggregate
VAR_SAMP	Calculates the sample variance value from a set of numbers.	Aggregate
VECTOR_SUB	The `VECTOR_SUB` function subtracts the second vector from the first vector and returns a vector which is the result of that subtraction.	Vector
VECTOR_SUM	Adds all vectors in a column and returns a vector which is the sum of those vectors.	Vector
WEEK	There are eight ways to number weeks, described below.	Date and Time
WEEKDAY	Extracts the day of the week from a date. Monday is 0, Tuesday is 1, etc.	Date and Time
YEAR	Extracts the year from the given datetime.	Date and Time

SQL Functions List

Was this article helpful?

Was this article helpful?