SuiteQL Supported and Unsupported Functions

ABS

returns the absolute value of n

ACOS

returns the arc cosine of n

ADD_MONTHS

returns the date date plus integer months

APPROX_COUNT_DISTINCT

returns the approximate number of rows that contain distinct values of expr

ASCII

returns the decimal representation in the database character set of the first character of char

ASCIISTR

takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set

ASIN

returns the arc sine of n

ATAN

returns the arc tangent of n

ATAN2

returns the arc tangent of n1 and n2

AVG

returns the average value of expr

BFILENAME

returns a BFILE locator that is associated with a physical LOB binary file on the server file system

BITAND

computes an AND operation on the bits of expr1 and expr2

CEIL

returns smallest integer greater than or equal to n

CHARTOROWID

converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to ROWIDdatatype

CHR

returns the character having the binary equivalent to n as a VARCHAR2 value

COALESCE

returns the first non-null expr in the expression list

COMPOSE

takes as its argument a string, or an expression that resolves to a string, in any datatype, and returns a Unicode string in its fully normalized form in the same character set as the input

CONCAT

concatenates char1 and char2 into one string

CORR

returns the coefficient of correlation of a set of number pairs

CORR_K

calculates the Kendall's tau-b correlation coefficient

CORR_S

calculates the Spearman's rho correlation coefficient

COS

returns the cosine of n (an angle expressed in radians)

COSH

returns the hyperbolic cosine of n

COUNT

returns the number of rows returned by the query

COVAR_POP

returns the population covariance of a set of number pairs

COVAR_SAMP

returns the sample covariance of a set of number pairs

CURRENT_DATE

returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE

CURRENT_TIMESTAMP

returns the current date and time in the session time zone

DECODE

compares expr to each search value one by one. If expr is equal to a search, then the Oracle database returns the corresponding result

DECOMPOSE

takes as its argument a string in any datatype and returns a Unicode string after decomposition in the same character set as the input

DENSE_RANK

computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER

EMPTY_BLOB

returns an empty LOB locator

EMPTY_CLOB

returns an empty LOB locator

EXP

returns e raised to the nth power

FLOOR

returns largest integer equal to or less than n

FROM_TZ

converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value

GREATEST

returns the greatest of a list of one or more expressions

INITCAP

returns char, with the first letter of each word in uppercase, all other letters in lowercase

INSTR

searches string for substring

LAST_DAY

returns the date of the last day of the month that contains date

LEAST

returns the least of the list of exprs

LENGTH

returns the length of char

LENGTH2

returns the length of the specified string, using UCS2 code points

LENGTH4

returns the length of the specified string, using UCS4 code points

LENGTHB

returns the length of the specified string, using bytes instead of characters

LENGTHC

returns the length of the specified string, using Unicode complete characters

LN

returns the natural logarithm of n, where n is greater than 0

LOCALTIMESTAMP

returns the current date and time in the session time zone in a value of datatype TIMESTAMP

LOG

computes the logarithm of an expression

LOWER

returns char, with all letters in lowercase

LPAD

returns expr1, left-padded to length n characters with the sequence of characters in expr2

LTRIM

removes from the left end of char all of the characters contained in set

MAX

returns the maximum value of expr

MEDIAN

is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value after the values are sorted

MIN

returns the minimum value of expr

MOD

returns the remainder of n2 divided by n1. Returns n2 if n1 is 0

MONTHS_BETWEEN

returns the number of months between date1 and date2

NANVL

useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. This function is useful for mapping NaN values to NULL

NEW_TIME

returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date

NEXT_DAY

returns the date of the first weekday named by char that is later than the date

NLSSORT

returns the string of bytes used to sort char

NLS_INITCAP

returns char, with the first letter of each word in uppercase, all other letters in lowercase

NLS_LOWER

returns char, with all letters in lowercase

NLS_UPPER

returns char, with all letters in uppercase

NULLIF

compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1

NVL

lets you replace null (returned as a blank) with a string in the results of a query

NVL2

lets you determine the value returned by a query based on whether a specified expression is null or not null

ORA_HASH

computes a hash value for a given expression

POWER

returns n2 raised to the n1 power

RANK

calculates the rank of a value in a set of values

REGEXP_INSTR

extends the functionality of the INSTR function by letting you search a string for a regular expression pattern

REGEXP_REPLACE

extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern

REGEXP_SUBSTR

extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern

REMAINDER

returns the remainder of n2 divided by n1

REPLACE

returns char with every occurrence of search_string replaced with replacement_string

ROUND

returns n rounded to integer places to the right of the decimal point

ROW_NUMBER

analytic function that assigns a unique number to each row to which it is applied

RPAD

returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary

RTRIM

removes from the right end of char all of the characters that appear in set

SIGN

returns the sign of n

SIN

returns the sine of n (an angle expressed in radians)

SINH

returns the hyperbolic sine of n

SOUNDEX

returns a character string containing the phonetic representation of char

SQRT

returns the square root of n

SUBSTR

returns a portion of char, beginning at character position, substring_length characters long

SUM

returns the sum of values of expr. You can use it as an aggregate or analytic function

SYS_EXTRACT_UTC

extracts the UTC from a datetime value with time zone offset or time zone region name

TAN

returns the tangent of n (an angle expressed in radians)

TANH

returns the hyperbolic tangent of n

TO_BINARY_DOUBLE

returns a double-precision floating-point number

TO_BINARY_FLOAT

returns a single-precision floating-point number

TO_CHAR

(number) converts n to a value of VARCHAR2 datatype

TO_CLOB

converts NCLOB values in a LOB column or other character strings to CLOB values

TO_DATE

converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype

TO_MULTI_BYTE

TO_MULTI_BYTE returns char with all of its single-byte characters converted to their corresponding multibyte characters

TO_NCHAR

converts a character string, CHAR, VARCHAR2, CLOB, or NCLOB value to the national character set

TO_NCLOB

converts CLOB values in a LOB column or other character strings to NCLOB values

TO_NUMBER

converts expr to a value of NUMBER datatype

TO_SINGLE_BYTE

returns char with all of its multibyte characters converted to their corresponding single-byte characters

TO_TIMESTAMP

converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype

TO_TIMESTAMP_TZ

converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype

TRANSLATE

returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string

TRUNC

(number) returns n1 truncated to n2 decimal places

TZ_OFFSET

returns the time zone offset corresponding to the argument based on the date the statement is executed

UNISTR

takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set

UPPER

returns char, with all letters in uppercase

VSIZE

returns the number of bytes in the internal representation of expr

WIDTH_BUCKET

lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size

BIT_LENGTH

—

BIT_XOR_AGG

—

CEILING

CEIL

CHAR

—

CHARINDEX

INSTR

CHAR_LENGTH

LENGTH

CHARACTER_LENGTH

LENGTH

CONVERT

—

COT

—

DATEDIFF

—

LCASE

LOWER

LEFT

SUBSTR

LISTAGG

—

LOCATE

INSTR

POSITION

INSTR

REPEAT

—

RIGHT

SUBSTR

SUBSTRING

SUBSTR

UCASE

UPPER