SQL functions

This list includes functions currently supported by Network Reporting.

abs

Absolute value.

ABS(number)

avg

Average (arithmetic mean) of all input values.

AVG(expression)

btrim

Removes the longest substring in matching_string consisting only of characters in string (a space by default) from the start and end of string.

BTRIM( string [, maching_string] )

cast

A type cast specifies a conversion from one data type to another.

CAST ( expression AS type )

ceil/ceiling

Returns the smallest integer equal to or larger than the input argument.

CEIL | CEILING(number)

charindex

Returns the numeric position of a substring within the specified string.

CHARINDEX(substring, string)

chr

Returns the character matching the specified ASCII value.

CHR(number)

coalesce (or nvl)

Returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display.

COALESCE | NVL(expression, expression, ...)

concat (or ||)

Concatenates two character strings.

CONCAT (string1, string2) string1 || string2 || string3 || ...

convert

Specifies a conversion from one data type to another.

CONVERT(type, expression)

convert_timezone

Converts a time from one time zone to another.

CONVERT_TIMEZONE ( ['source_timezone',] 'target_timezone', 'timestamp')

count

Returns the number of input values for which the value of expression is not null.

COUNT(expression)

current_date

Returns a date in the current session time zone in the format YYYY-MM-DD.

CURRENT_DATE

dateadd

Increments a date or time stamp by the specified interval.

DATEADD( datepart, interval, {date|timestamp} )

date_cmp

Compares two dates, returning an integer. Identical dates return 0. If the first date is greater, 1 is returned. If the second date is greater, -1 is returned.

DATE_CMP(date1, date2)

datediff

Returns the difference between parts of two date or time expressions.

DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )

date_part

Extracts part of a date from a date or time stamp.

DATE_PART ( datepart, {date|timestamp} )

date_trunc

Truncates a time stamp on the date part specified, such as hour, day, week, or month.

DATE_TRUNC('datepart', timestamp)

exp

Executes the natural exponential function (base e) to the power of the numeric expression provided. Returns the exponential value in scientific notation for a numeric expression.

EXP( expression )

extract

Returns part of a date (day, month, year) from a time stamp.

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp } )

floor

Returns the largest integer that is not greater than the argument.

FLOOR(number)

getdate

Retrieves the current date and time in the time zone of the current instance.

GETDATE ()

last_day

Returns the date of the last day of the month that contains the specified date value.

LAST_DAY ( { date | timestamp } )

len/length

Returns the length of the specified string as the number of characters.

LEN (expression)

listagg

Concatenates values into a single string, by the provided delimiter. Using the DISTINCT argument, you can eliminate duplicate values from the specified expression before concatenating the values into a single string.

LISTAGG( [DISTINCT] aggregate_expression) [, 'delimiter'])

Supports the WITHIN GROUP (ORDER BY <list_type>) clause so you can specify the sort order of the aggregated values.

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]

ln

Natural logarithm.

LN(expression)

log

Returns the base 10 logarithm of a number.

LOG(number)

lower

Converts a string to lower case.

LOWER(string)

lpad

Prepends specified characters to a string up to the specified length. If the original string is longer than the specified length, the string is truncated to that length. The default fill character is a blank.

LPAD (string1, length, [ string2 ])

ltrim

Trims a specified set of characters from the beginning of a string.

LTRIM( string, 'trim_chars' )

max

Maximum value of an expression across all input values.

MAX(expression)

md5

Calculates the MD5 hash of string, returning the result in hexadecimal.

MD5(string)

median

Calculates a median value from a range of values.

MEDIAN ( median_expression )

min

Minimum value of an expression across all input values.

MIN(expression)

mod

Remainder of two numeric values; the first is divided by the second.

mod(number1, number2)

next_day

Returns the date of the first instance of a specified day that is later than the specified date.

NEXT_DAY ( { date | timestamp }, day )

position

Returns a substring within a string.

POSITION(<search-substring> IN <source-string> )

power

A specified value raised to the power of another specified value.

POWER(<base>, <exponent>)

random

Random value between 0.0 and 1.0.

RANDOM()

round

Round a number to the nearest decimal or integer.

ROUND (decimal, scale)

regexp_count

Searches a string for a regex pattern and returns a value indicating the number of times the pattern occurs in the string. If no match is found, the function returns 0.

REGEXP_COUNT ( source_string, pattern [, position ] )

regexp_instr

Searches a string for a regex pattern and returns a a value indicating the beginning position of the matched substring. If no match is found, the function returns 0.

REGEXP_INSTR ( source_string, pattern [, position ] )

regexp_replace

Searches a string for a regex pattern and replaces every occurrence of the pattern with the specified string.

REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] )

regexp_substr

Returns the characters extracted from a string by searching for a regex pattern.

REGEXP_SUBSTR ( source_string, pattern [, position ] )

repeat

Repeats a string the specified number of times. If the input parameter is numeric, it is treated as a string.

REPEAT(string, integer)

replace

Replaces all occurrences of a set of characters within an existing string with another set of specified characters.

REPLACE(string1, old_chars, new_chars)

reverse

Returns the characters of a string in reverse order. This function works on numeric and date data types as well as the character data type.

REVERSE ( expression )

rpad

Appends specified characters to a string up to the specified length. If the original string is longer than the specified length, the string is truncated to that length. The default fill character is a blank.

RPAD (string1, length, [ string2 ])

rtrim

Trims a specified set of characters from the end of a string.

RTRIM( string, trim_chars )

split_part

Splits a string on the specified delimiter and return part based on a specified position.

SPLIT_PART(string, delimiter, part)

sqrt

Returns the square root for the specified value.

SQRT(expression)

strpos

Returns the position of a substring within a specified string.

STRPOS(string, substring )

substring

Returns a subset of characters extracted from a string based on specified characters and position elements.

SUBSTRING(string, start_position, number_characters )

sum

Sum of an expression across all input values.

SUM ( [ DISTINCT | ALL ] expression )

timeofday

Returns the current date and time.

TIMEOFDAY()

timestamp_cmp

Compares two time stamps, returning an integer. Identical time stamps return 0. If the first time stamp is greater, 1 is returned. If the second time stamp is greater, -1 is returned.

TIMESTAMP_CMP(timestamp1, timestamp2)

timestamptz_cmp

Compares of two time stamps including time zones, returning an integer. Identical time stamps return 0. If the first time stamp is greater, 1 is returned. If the second time stamp is greater, -1 is returned.

TIMESTAMPTZ_CMP(timestamptz1, timestamptz2)

to_char

Converts a time stamp to a string.

TO_CHAR (timestamp_expression | numeric_expression , 'format')

to_date

Converts a string to a date.

TO_DATE (string, format)

to_number

Converts a string to a numeric.

TO_NUMBER (string, format)

to_timestamp

Converts a string to a time stamp.

TO_TIMESTAMP ('timestamp', 'format')

trunc

Truncates a number and right-fills it with zeros from the position (integer) specified. If no position is specified, the number is truncated as a whole number. This function also truncates a timestamp and returns a date.

TRUNC(number [ , integer ] |
 timestamp )

upper

Converts a string to uppercase.

UPPER(string)