SQL Functions ABS – A numeric function that returns the absolute value of a numeric expression. ACOS – A scalar numeric function that returns the arc-cosine, in radians, of a given cosine. ASCII – A string function that returns the integer ASCII code value of the first (leftmost) character of a string expression. ASIN – A scalar numeric function that returns the arc-sine, in radians, of the sine of an angle. ATAN – A scalar numeric function that returns the arc-tangent, in radians, of the tangent of an angle. ATAN2 – A scalar numeric function that takes two coordinates and returns the arc-tangent angle in radians. CAST – A function that converts a given expression to a specified data type. CEILING – A numeric function that returns the smallest integer greater than or equal to a given numeric expression. CHAR – A string function that returns the character that has the ASCII code value specified in a string expression. CHARACTER_LENGTH – A function that returns the number of characters in an expression. CHARINDEX – A string function that returns the position of a substring within a string, with optional search start point. CHAR_LENGTH – A function that returns the number of characters in an expression. COALESCE – A function that returns the value of the first expression that is not NULL. CONCAT – A scalar string function that returns a character string as a result of concatenating two character expressions. CONVERT – A function that converts a given expression to a specified data type. COS – A scalar numeric function that returns the cosine, in radians, of an angle. COT – A scalar numeric function that returns the cotangent, in radians, of an angle. CURDATE – A scalar date/time function that returns the current local date. CURRENT_DATE – A date/time function that returns the current local date. CURRENT_TIME – A date/time function that returns the current local time. CURRENT_TIMESTAMP – A date/time function that returns the current local date and time. CURTIME – A scalar date/time function that returns the current local time. DATABASE – A scalar string function that returns the database name qualifier. DATALENGTH – A function that returns the number of characters in an expression. DATE – A function that takes a timestamp and returns a date. DATEADD – A date/time function that returns a timestamp calculated by adding or subtracting a number of date part units (such as hours or days) to a date or timestamp. DATEDIFF – A date/time function that returns an integer difference for a specified datepart between two dates. DATENAME – A date/time function that returns a string representing the value of the specified part of a date/time expression. DATEPART – A date/time function that returns an integer representing the value of the specified part of a date/time expression. DAY – A date function that returns the day of the month for a date expression. DAYNAME – A date function that returns the name of the day of the week for a date expression. DAYOFMONTH – A date function that returns the day of the month for a date expression. DAYOFWEEK – A date function that returns the day of the week as an integer for a date expression. DAYOFYEAR – A date function that returns the day of the year as an integer for a date expression. DECODE – A function that evaluates a given expression and returns a specified value. DEGREES – A numeric function that converts radians to degrees. %EXACT – A collation function that converts characters to the EXACT collation format. EXP – A scalar numeric function that returns the exponential (inverse of natural logarithm) of a number. %EXTERNAL – A format-transformation function that returns an expression in DISPLAY format. $EXTRACT – A string function that extracts characters from a string by position. $FIND – A string function that returns the end position of a substring within a string, with optional search start point. FLOOR – A numeric function that returns the largest integer less than or equal to a given numeric expression. GETDATE – A date/time function that returns the current local date and time. GETUTCDATE – A date/time function that returns the current UTC date and time. GREATEST – A function that returns the greatest value from a series of expressions. HOUR – A time function that returns the hour for a datetime expression. IFNULL – A function that tests for NULL and returns the appropriate expression. INSTR – A string function that returns the position of a substring within a string, with an optional search start point and occurrence count. %INTERNAL – A format-transformation function that returns an expression in LOGICAL format. ISNULL – A function that tests for NULL and returns the appropriate expression. ISNUMERIC – A numeric function that tests for a valid number. JSON_ARRAY – A conversion function that returns data as a JSON array. JSON_OBJECT – A conversion function that returns data as a JSON object. $JUSTIFY – A function that right-aligns a value within a specified width, optionally rounding to a specified number of fractional digits. LAST_DAY – A date function that returns the date of the last day of the month for a date expression. LAST_IDENTITY – A scalar function that returns the identity of the last row inserted, updated, deleted, or fetched. LCASE – A case-transformation function that converts all uppercase letters in a string to lowercase letters. LEAST – A function that returns the least value from a series of expressions. LEFT – A scalar string function that returns a specified number of characters from the beginning (leftmost position) of a string expression. LEN – A string function that returns the number of characters in a string expression. LENGTH – A string function that returns the number of characters in a string expression. $LENGTH – A string function that returns the number of characters or the number of delimited substrings in a string. $LIST – A list function that returns elements in a list. $LISTBUILD – A list function that builds a list from strings. $LISTDATA – A list function that indicates whether the specified element exists and has a data value. $LISTFIND – A list function that searches a specified list for the requested value. $LISTFROMSTRING – A list function that creates a list from a string. $LISTGET – A list function that returns an element in a list or a specified default value. $LISTLENGTH – A list function that returns the number of elements in a specified list. $LISTSAME – A list function that compares two lists and returns a boolean value. $LISTTOSTRING – A list function that creates a string from a list. LOG – A scalar numeric function that returns the natural logarithm of a given numeric expression. LOG10 – A scalar numeric function that returns the base-10 logarithm of a given numeric expression. LOWER – A case-transformation function that converts all uppercase letters in a string expression to lowercase letters. LPAD – A string function that returns a string left-padded to a specified length. LTRIM – A string function that returns a string with the leading blanks removed. %MINUS – A collation function that converts numbers to canonical collation format, then inverts the sign. MINUTE – A time function that returns the minute for a datetime expression. MOD – A scalar numeric function that returns the modulus (remainder) of a number divided by another. MONTH – A date function that returns the month as an integer for a date expression. MONTHNAME – A date function that returns the name of the month for a date expression. NOW – A date/time function that returns the current local date and time. NULLIF – A function that returns NULL if two expressions have the same value. NVL – A function that tests for NULL and returns the appropriate expression. %OBJECT – A scalar function that opens a stream object and returns the corresponding oref. %ODBCIN – A format-transformation function that returns an expression in Logical format. %ODBCOUT – A format-transformation function that returns an expression in ODBC format. %OID – A scalar function that returns OID of an ID field. PI – A scalar numeric function that returns the constant value of pi. $PIECE – A string function that returns a substring identified by a delimiter. %PLUS – A collation function that converts numbers to canonical collation format. POSITION – A string function that returns the position of a substring within a string. POWER – A numeric function that returns the value of a given expression raised to the specified power. PREDICT – A function that applies a specified trained model to predict the result for each input row provided. PROBABILITY – A function that applies a specified trained model to return the probability that the specified label is the predicted label value. This allows you to evaluate the relative strength of predictions of that value. QUARTER – A date function that returns the quarter of the year as an integer for a date expression. RADIANS – A numeric function that converts degrees to radians. REPEAT – A string function that repeats a string a specified number of times. REPLACE – A string function that replaces a substring within a string. REPLICATE – A string function that repeats a string a specified number of times. REVERSE – A scalar string function that returns a character string in reverse character order. RIGHT – A scalar string function that returns a specified number of characters from the end (rightmost position) of a string expression. ROUND – A numeric function that rounds or truncates a number at a specified number of digits. RPAD – A string function that returns a string right-padded to a specified length. RTRIM – A string function that returns a string with the trailing blanks removed. SEARCH_INDEX – A function that returns a set of values from the index’s Find() method. SECOND – A time function that returns the second for a datetime expression. SIGN – A numeric function that returns the sign of a given numeric expression. SIN – A scalar numeric function that returns the sine, in radians, of an angle. SPACE – A string function that returns a string of spaces. %SQLSTRING – A collation function that sorts values as strings. %SQLUPPER – A collation function that sorts values as uppercase strings. SQRT – A numeric function that returns the square root of a given numeric expression. SQUARE – A scalar numeric function that returns the square of a number. STR – A function that converts a numeric to a string. STRING – A function that converts and concatenates expressions into a string. STUFF – A string function that replaces a substring within a string. SUBSTR – A string function that returns a substring derived from a specified string expression. SUBSTRING – A string function that returns a substring from data of any data type, including stream data. SYSDATE – A date/time function that returns the current local date and time. %SYSTEM_SQL.DefaultSchema() – A function that returns the default schema for the current process in the current namespace. TAN – A scalar numeric function that returns the tangent, in radians, of an angle. TIMESTAMPADD – A scalar date/time function that returns a new timestamp calculated by adding a number of intervals of a specified date part to a timestamp. TIMESTAMPDIFF – A scalar date/time function that returns an integer count of the difference between two timestamps for a specified date part. TO_CHAR – A string function that converts a date, timestamp, or number to a formatted character string. TO_DATE – A date function that converts a formatted string to a date. TO_NUMBER – A string function that converts a string expression to a value of NUMERIC data type. TO_POSIXTIME – A date/time function that converts a formatted date string to a %PosixTime timestamp. TO_TIMESTAMP – A date function that converts a formatted string to a timestamp. $TRANSLATE – A string function that performs character-for-character replacement. TRIM – A string function that returns a character string with specified leading and/or trailing characters removed. TRUNCATE – A scalar numeric function that truncates a number at a specified number of digits. %TRUNCATE – A collation function that truncates a string to the specified length and applies EXACT collation. $TSQL_NEWID – A function that returns a globally unique ID. UCASE – A case-transformation function that converts all lowercase letters in a string to uppercase letters. UNIX_TIMESTAMP – A date/time function that converts a date expression to a UNIX timestamp. UPPER – A case-transformation function that converts all lowercase letters in a string expression to uppercase letters. USER – A function that returns the user name of the current user. WEEK – A date function that returns the week of the year as an integer for a date expression. XMLCONCAT – A function that concatenates XML elements. XMLELEMENT – A function that formats an XML markup tag to enclose one or more expression values. XMLFOREST – A function that formats multiple XML markup tags to enclose expression values. YEAR – A date function that returns the year for a date expression.
