SQL Functions List

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 or a 256-bit key.

String

AES_ENCRYPT

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

String

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

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. 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 datatype.

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 datatype.

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

Returns the scalar product, or dot product, of two vector input values.

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

Returns the scalar Euclidean distance between two vector input values. It is a scalar function.

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

ISNUMERIC

Determines whether the provided expression is a valid numeric type.

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_ARRAY_UNPACK

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

Vector

JSON_BUILD_OBJECT

Converts a comma-separated argument list to a JSON object.

JSON

JSON_DELETE_KEY

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

JSON

JSON_EXTRACT_<type>

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

JSON

JSON_GET_TYPE

Returns the Javascript type of the given JSON object.

JSON

JSON_KEYS

Returns the top-level keys of a JSON object in the form of a JSON array.

JSON

JSON_LENGTH

Computes the length of a JSON map or array.

JSON

JSON_MATCH_ANY

This function can find data within a JSON document that matches the specified filter conditions. It is more succinct than extracting values and checking them using regular SQL filters.

JSON

JSON_PRETTY

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

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 SingleStoreDB Cloud 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 tables created with a FULLTEXT index, the columnstore columns in that index can be searched through 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 SingleStoreDB Cloud, percentile functions are available as window functions and aggregate functions.

Window

PERCENTILE_DISC

In SingleStoreDB Cloud, 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

REVERSE

Reverses the character order of a string or returns a NULL if the parameter is NULL.

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

SCALAR_VECTOR_MUL

Multiplies each element in the vector blob with the specified scalar value and returns a single vector blob.

Vector

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

SECRET

Provides the ability to hide credentials from queries.

String

SET

Sets a value for a user-defined variable for use in SQL statements. This is analogous to the SELECT ... INTO user defined variables discussed here: SELECT ... INTO User Defined Variable.

User-Defined Variables

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 SingleStoreDB Cloud 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

SYS_GUID and UUID

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

Identifier Generation

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, or a single row to a JSON object.

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_ADD

Adds two input vectors and returns a single vector blob.

Vector

VECTOR_ELEMENTS_SUM

Returns the sum of all elements of a vector expression.

Vector

VECTOR_KTH_ELEMENT

Returns the k-th element in a vector expression where k is 0-indexed.

Vector

VECTOR_MUL

Multiplies two vector blobs and returns a single vector blob.

Vector

VECTOR_NUM_ELEMENTS

Return the number of elements in a vector.

Vector

VECTOR_SORT

Sorts the elements in the vector expression in ascending or descending order.

Vector

VECTOR_SUB

Returns a vector blob from two input vectors by subtracting the second vector from the first vector.

Vector

VECTOR_SUBVECTOR

Derives a vector expression from another vector expression.

Vector

VECTOR_SUM

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

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