Semarchy xDM semql reference guide

boldface

Boldface type indicates graphical user interface elements associated with an action, or a product specific term or concept.

italic

Italic type indicates special emphasis or placeholder variable that you need to provide.

monospace

Monospace type indicates code example, text or commands that you enter.

ABS

Returns the absolute value of number.

ACOS

Returns the arc cosine of number. The argument number must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi, expressed in radians.

ADD_MONTHS

Returns the date "date" plus "integer" months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer.

ASCII

Returns the decimal representation in the database character set of the first character of string.

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. Non-ASCII characters are converted to the form xxxx, where xxxx represents a UTF-16 code unit.

ASIN

Returns the arc sine of number. The argument number must be in the range of -1 to 1, and the function returns a value in the range of -pi/2 to pi/2, expressed in radians.

ATAN

Returns the arc tangent of number. The argument number can be in an unbounded range and the function returns a value in the range of -pi/2 to pi/2, expressed in radians.

ATAN2

Returns the arc tangent of number1 and number2. The argument number1 can be in an unbounded range and the function returns a value in the range of -pi to pi, depending on the signs of number1 and number2, expressed in radians. ATAN2(n1,n2) is the same as ATAN2(n1/n2).

BIN_TO_NUM

Converts a bit vector to its equivalent number. Each argument to this function represents a bit in the bit vector. This function takes as arguments any numeric data type, or any nonnumeric data type that can be implicitly converted to a number. Each expr must evaluate to 0 or 1.

BITAND

Computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer.

CEIL

Returns the smallest integer greater than or equal to number.

CHR

Returns the character having the binary equivalent to number as a string value in the database character set.

COALESCE

Returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

COMPOSE

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

CONCAT

Returns string1 concatenated with string2. This function is equivalent to the concatenation operator.

CONVERT

Converts a character string from one character set to another.

COS

Returns the cosine of number (an angle expressed in radians).

COSH

Returns the hyperbolic cosine of number.

CURRENT_DATE

Returns the current date in the session time zone, in a value in the Gregorian calendar.

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone. If you omit precision, then the default is 6.

DBTIMEZONE

Returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name.

DECODE

Compares expr to each search value one by one. If expr is equal to a search, then it returns the corresponding result. If no match is found, then it returns default. If default is omitted, then Oracle returns null.

DECOMPOSE

Takes as its argument a string in any data type and returns a Unicode string after decomposition in the same character set as the input. For example, an o-umlaut code point will be returned as the "o" code point followed by an umlaut code point.

EXP

Returns e raised to the number-th power, where e = 2.71828183 …​ The function returns a value of the same type as the argument.

EXTRACT_DAY

Extracts and returns the day from expr. expr must be a valid ANSI date.

EXTRACT_HOUR

Extracts and returns the hour from expr. expr must be a valid ANSI datetime.

EXTRACT_MINUTE

Extracts and returns the minute from expr. expr must be a valid ANSI datetime.

EXTRACT_MONTH

Extracts and returns the month from expr. expr must be a valid ANSI date.

EXTRACT_SECOND

Extracts and returns the second from expr. expr must be a valid ANSI datetime.

EXTRACT_TIMEZONE_ABBR

Extracts and returns the abbreviation of the timezone from expr. expr must be a valid ANSI datetime including a timezone.

EXTRACT_TIMEZONE_HOUR

Extracts and returns the hour of the timezone from expr. expr must be a valid ANSI datetime including a timezone.

EXTRACT_TIMEZONE_MINUTE

Extracts and returns the minute of the timezone from expr. expr must be a valid ANSI datetime including a timezone.

EXTRACT_TIMEZONE_REGION

Extracts and returns the region of the timezone from expr. expr must be a valid ANSI datetime including a timezone.

EXTRACT_YEAR

Extracts and returns the year from expr. expr must be a valid ANSI date.

FLOOR

Returns largest integer equal to or less than number.

FROM_TZ

Converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. Time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

GREATEST

Returns the greatest of the list of one or more expressions.

HEXTORAW

Converts string containing hexadecimal digits to a raw value.

INITCAP

Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

INSTR

Searches string for substring using the input character set. The function returns an integer indicating the position of the first occurrence. Optionally, you can indicate the starting position for the search and the occurrence to search for.

INSTR2

Searches string for substring using UC2 code points. The function returns an integer indicating the position of the first occurrence. Optionally, you can indicate the starting position for the search and the occurrence to search for.

INSTR4

Searches string for substring using UC4 code points. The function returns an integer indicating the position of the first occurrence. Optionally, you can indicate the starting position for the search and the occurrence to search for.

INSTRB

Searches string for substring using bytes instead of character. The function returns an integer indicating the position of the first occurrence. Optionally, you can indicate the starting position for the search and the occurrence to search for.

INSTRC

Searches string for substring using Unicode complete characters. The function returns an integer indicating the position of the first occurrence. Optionally, you can indicate the starting position for the search and the occurrence to search for.

LAST_DAY

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

LEAST

Returns the least of the list of expressions.

LENGTH

Returns the length of string. Length is calculated using characters as defined by the input character set.

LENGTH2

Returns the length of string. Length is calculated using characters as defined by the UC2 code point.

LENGTH4

Returns the length of string. Length is calculated using characters as defined by the UC4 code point.

LENGTHB

Returns the length of string. Length is calculated using bytes instead of characters.

LENGTHC

Returns the length of string. Length is calculated using Unicode complete characters.

LN

Returns the natural logarithm of number, where number is greater than 0.

LOCALTIMESTAMP

Returns the current date and time in the session time zone.

LOG

Returns the logarithm, base number2, of number1. The base number1 can be any positive value other than 0 or 1 and number2 can be any positive value.

LOWER

Returns char, with all letters lowercase.

LPAD

Returns expr1, left-padded to length number characters with the sequence of characters in expr2. If you do not specify expr2, then the default is a single blank.

LTRIM

Removes from the left end of string all of the characters contained in set_of_chars. If you do not specify set_of_chars, it defaults to a single blank.

MOD

Returns the remainder of number2 divided by number1. Returns number2 if number1 is 0.

MONTHS_BETWEEN

Returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative.

NANVL

If number1 is not a number (NaN) then NANVL returns number2. Otherwise, it returns number1.

NCHR

Returns the character having the binary equivalent to number as a string value in the national character set.

NEW_TIME

Returns the date and time (given in timezone 1) converted in time zone timezone2.

NEXT_DAY

Returns the date of the first weekday named by day_name that is later than the date date.

NLSSORT

Returns a collation key for string, that is a string of bytes used to sort strings. lsparam is in the form NLS_SORT=sort where sort is either BINARY or a liguistic sort sequence. This function manages language-specific sorting.

NLS_INITCAP

Returns string with the first letter of each word in uppercase and all other letters in lowercase. nlsparam is in the form NLS_SORT=sort where sort is either BINARY or a liguistic sort sequence. This function manages language-specific characters case changes.

NLS_LOWER

Returns string with all letters in lowercase. nlsparam is in the form NLS_SORT=sort where sort is either BINARY or a liguistic sort sequence.

NLS_UPPER

Returns string with all letters in uppercase. nlsparam is in the form NLS_SORT=sort where sort is either BINARY or a liguistic sort sequence.

NULLIF

Compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1. The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

NUMTODSINTERVAL

Converts number to an INTERVAL DAY TO SECOND literal. The value for interval_unit specifies the unit of number and must resolve to one of the following string values: 'DAY', 'HOUR', 'MINUTE', 'SECOND'.

NUMTOYMINTERVAL

Converts number to an INTERVAL YEAR TO MONTH literal. The value for interval_unit specifies the unit of number and must resolve to one of the following string values: 'YEAR', 'MONTH'.

NVL

If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

NVL2

If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

ORA_HASH

Computes a hash value for a given expression. The expr argument determines the data for which you want to compute a hash value. The optional max_bucket argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295. The optional seed_value argument produces many different results for the same set of data.

POWER

Returns number2 raised to the number1 power. The base number2 and the exponent number1 can be any numbers, but if number2 is negative, then number1 must be an integer.

RAWTOHEX

Converts raw to a character value containing its hexadecimal equivalent.

REGEXP_COUNT

Extends the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string, starting at position. match_param contains one of more of the following values: i - Case-insensitive match, c - Case-sensitive match, n - Allow '.' to match even the newline character, m - treat input as multiple lines, x - ignore whitespaces.

REGEXP_INSTR

Extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0.

REGEXP_REPLACE

Extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string.

REGEXP_SUBSTR

Extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string.

REMAINDER

Returns the remainder of number2 divided by number1.

REPLACE

Returns string with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then string is returned.

ROUND

Returns date_or_number rounded to the unit specified by the format model fmt_or_integer.

RPAD

Returns expr1, right-padded to length number characters with expr2, replicated as many times as necessary. If you do not specify expr2, then it defaults to a single blank.

RTRIM

Removes from the right end of string all of the characters that appear in set_of_chars. If you do not specify set_of_chars, then it defaults to a single blank.

SEM_BOOLEAN_TO_CHAR

Converts a boolean to its standard string representation.

SEM_CONCAT

Returns a string concatenating the input values with the separator, optionally removing null values

SEM_EDIT_DISTANCE

Calculates the distance between two strings, that is the number of insertions, deletions or substitutions required to transform string1 into string2. If one or both of the strings are null the distance will be the largest integer value (2147483647). Note that this function measures the distance in number of bytes and not in characters. As a consequence, strings stored using variable-width characters sets (UTF-8, for example) can cause counter-intuitive results. It is recommended to convert these strings to a fixed-width character set (AL16UTF16, for example) prior to passing them to this function.

SEM_EDIT_DISTANCE_SIMILARITY

Calculates the distance between string1 into string2 (as described in the SEM_EDIT_DISTANCE function), and returns the Normalized value of the Edit Distance between two Strings. The value is between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0.

SEM_INSTR

Finds the location of a substring within a specified string.

SEM_JARO_WINKLER

Calculates the measure of agreement between two strings using Jaro-Winkler method. The value is between 0 (no match) and 1 (perfect match). If one or both strings are null the result will be 0.

SEM_JARO_WINKLER_SIMILARITY

Calculates the measure of agreement between two strings using Jaro-Winkler method, and returns a score between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0.

SEM_NGRAMS_SIMILARITY

Calculates the measure of agreement between two strings using the Dice’s coefficient similarity measure applied to the n-grams of the strings, and returns a score between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0. The ngrams_length parameter defines the length of the n-grams (2 by default).

SEM_NORMALIZE

Returns a string with Latin (supplement, Extended-A and Extended-B) characters converted into their ASCII equivalents, and other (space and non-alphanumeric) characters eliminated.

SEM_NUMBER_TO_CHAR

Converts a number to its standard string representation.

SEM_SUBSTRING

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using characters as defined by the input character set.

SEM_TIMESTAMP_TO_CHAR

Converts a timestamp to its standard string representation.

SEM_TO_CHAR

Converts a string or a number to its standard string representation.

SEM_UUID_TO_CHAR

Converts a UUID to its standard string representation.

SEQ_NEXTVAL

Get the next value of a sequence. Note that this function is not supported in Enrichers.

SIGN

Returns the sign of number. The sign is: -1 if n<0, 0 if n=0, 1 if n>0.

SIN

Returns the sine of number (an angle expressed in radians).

SINH

Returns the hyperbolic sine of number.

SOUNDEX

Returns a character string containing the phonetic representation of string. This function lets you compare words that are spelled differently, but sound alike in English.

SQRT

Returns the square root of number.

STANDARD_HASH

Computes a hash value for a given expression and returns it in a RAW value. The optional method lets you choose the hash algorithm (defaults to SHA1) in the following list: SHA1, SHA256, SHA384, SHA512 and MD5. This function requires Oracle version 12c or above.

SUBSTR

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using characters as defined by the input character set.

SUBSTR2

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using UCS2 code points.

SUBSTR4

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using UCS4 code points.

SUBSTRB

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using bytes instead of characters.

SUBSTRC

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using Unicode complete characters.

SYSDATE

Returns the current date and time set for the operating system on which the database resides.

SYSTIMESTAMP

Returns the system date, including fractional seconds and time zone, of the system on which the database resides.

SYS_CONTEXT

Returns the value of parameter associated with the context namespace.

SYS_EXTRACT_UTC

Extracts the UTC (Coordinated Universal Time—​formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name.

SYS_GUID

Generates and returns a globally unique identifier (RAW value) made up of 16 bytes.

TAN

Returns the tangent of number (an angle expressed in radians).

TANH

Returns the hyperbolic tangent of number.

TO_BINARY_DOUBLE

Returns a double-precision floating-point number.

TO_BINARY_FLOAT

Returns a single-precision floating-point number.

TO_CHAR

Converts expr to its string representation optionally using fmt and nlsparam for the conversion.

TO_CLOB

Converts expr to a CLOB (large string)

TO_DATE

Converts string to a date value. The fmt is a datetime model format specifying the format of string. If you omit fmt, then string must be in the default date format. If fmt is J, for Julian, then string must be an integer.

TO_DSINTERVAL

Converts a character string to an INTERVAL DAY TO SECOND value.

TO_MULTI_BYTE

Returns string with all of its single-byte characters converted to their corresponding multibyte characters.

TO_NUMBER

Converts expr to a number value using the optional format model fmt and nlsparam.

TO_SINGLE_BYTE

Returns string with all of its multibyte characters converted to their corresponding single-byte characters.

TO_TIMESTAMP

Converts string a timestamp value. The optional fmt specifies the format of string.

TO_TIMESTAMP_TZ

Converts string to a TIMESTAMP WITH TIME ZONE value. The optional fmt specifies the format of string.

TO_YMINTERVAL

Converts string to an INTERVAL YEAR TO MONTH type.

TRANSLATE

Returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in string, then they are removed from the return value.

TRIM

Removes from the left and right ends of string all of the characters contained in set_of_chars. If you do not specify set_of_chars, it defaults to a single blank.

TRUNC

When expr is a date, returns expr with the time portion of the day truncated to the unit specified by the format model fmt_or_number. If you omit fmt_or_number, then date is truncated to the nearest day. When expr is a number, returns expr truncated to fmt_or_number decimal places. If fmt_or_number is omitted, then expr is truncated to 0 places.

UNISTR

Takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. The national character set of the database can be either AL16UTF16 or UTF8. UNISTR provides support for Unicode string literals by letting you specify the Unicode encoding value of characters in the string.

UPPER

Returns string with all letters uppercase.

WIDTH_BUCKET

Lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. For a given expression, the function returns the bucket number into which the value of this expression would fall after being evaluated. Expr must evaluate to a numeric or datetime. Min_value and max_value are expressions that resolve to the end points of the acceptable range for expr. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null.

ABS

Returns the absolute value

ACOS

Returns the inverse cosine

AGE

Subtracts arguments, producing a symbolic result that uses years and months, rather than just days

ARRAY_AGG

Concatenates input values, including nulls, into an array. If the inputs are arrays, concatenates them into an array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)

ASCII

ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.

ASIN

Returns the inverse sine

ATAN

Returns the inverse tangent

ATAN2

Returns the inverse tangent of number_1/number_2

AVG

Returns the average (arithmetic mean) of all input values

BIT_AND

Returns the bitwise AND of all non-null input values, or null if none

BIT_LENGTH

Returns the number of bits in string

BIT_OR

Returns the bitwise OR of all non-null input values, or null if none

BOOL_AND

Returns true if all input values are true, otherwise false

BOOL_OR

Returns true if at least one input value is true, otherwise false

BTRIM

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

CBRT

Returns the cube root

CEIL

Returns the nearest integer greater than or equal to argument

CEILING

Returns the nearest integer greater than or equal to argument (same as ceil)

CHAR_LENGTH

Returns the number of characters in string

CHR

Returns the character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes.

CLOCK_TIMESTAMP

Returns the current date and time (changes during statement execution)

COALESCE

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.

CONCAT

Concatenates the text representations of all the arguments. NULL arguments are ignored.

CONCAT_WS

Concatenates all but the first argument with separators. The first argument is used as the separator string. NULL arguments are ignored.

CONVERT

Converts string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding.

CONVERT_FROM

Converts string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding.

CONVERT_TO

Converts string to dest_encoding.

COS

Returns the cosine

COT

Returns the cotangent

COUNT

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

CURRENT_DATE

Returns the current date

CURRENT_TIME

Returns the current time of day

CURRENT_TIMESTAMP

Returns the current date and time (at the beginning of current the transaction)

CURRVAL

Return value most recently obtained with nextval for specified sequence

DATERANGE

Creates a range of dates. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

DATE_PART

Get subfield from a timestamp or an interval. The part to extract is defined by the text.

DATE_TRUNC

Truncate timestamp or interval to the precision specified in the text.

DECODE

Decodes binary data from textual representation in string. Options for format are same as in encode.

DEGREES

Converts radians to degrees

DIV

Returns the integer quotient of number_1/number_2

ENCODE

Encodes binary data into a textual representation. Supported formats are: base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (\nnn) and doubles backslashes.

EVERY

Equivalent to bool_and

EXP

Returns the exponential

FLOOR

Returns the nearest integer less than or equal to argument

FORMAT

Formats the arguments according to format_string. This function is similar to the C function sprintf.

GET_BIT

Extract bit from string

GET_BYTE

Extract byte from string

GREATEST

The GREATEST function selects the largest value from a list of any number of expressions.

INITCAP

Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

INT4RANGE

Creates a range of integers. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

INT8RANGE

Creates a range of bigints. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

ISEMPTY

Returns a boolean indicating whether the range empty.

ISFINITE

Tests for finite date, interval or timestamp (not +/-infinity)

JSONB_AGG

Aggregates values as a JSON array

JSONB_OBJECT_AGG

Aggregates name/value pairs as a JSON object

JSON_AGG

Aggregates values as a JSON array

JSON_OBJECT_AGG

Aggregates name/value pairs as a JSON object

JUSTIFY_DAYS

Adjust interval so 30-day time periods are represented as months

JUSTIFY_HOURS

Adjust interval so 24-hour time periods are represented as days

JUSTIFY_INTERVAL

Adjust interval using justify_days and justify_hours, with additional sign adjustments

LASTVAL

Return value most recently obtained with nextval for any sequence

LEAST

The LEAST function selects the smallest value from a list of any number of expressions.

LEFT

Returns the first n characters in the string. When n is negative, return all but last n characters.

LENGTH

Returns the number of characters in string with an optional given encoding. The string must be valid in this encoding.

LN

Returns the natural logarithm.

LOCALTIME

Returns the current time of day

LOCALTIMESTAMP

Return the current date and time (at start of current transaction)

LOG

Returns the logarithm in base 10.

LOWER

Convert string to lower case

LOWER_INC

Returns a boolean indicating whether the lower bound of the range is inclusive.

LOWER_INF

Returns a boolean indicating whether the lower bound of the range is infinite.

LPAD

Fills up the string to length length by prepending the characters fill_text (a space by default). If the string is already longer than length then it is truncated (on the right).

LTRIM

Removes the longest string containing only characters from characters (a space by default) from the start of string

MAKE_DATE

Create date from integer year, month and day fields

MAKE_INTERVAL

Create interval from years, months, weeks, days, hours, minutes and seconds fields. If a field is left empty, it defaults to zero.

MAKE_TIME

Create time from hour, minute and seconds fields

MAKE_TIMESTAMP

Create timestamp from year, month, day, hour, minute and seconds fields

MAKE_TIMESTAMPTZ

Create timestamp with time zone from year, month, day, hour, minute and seconds fields; if timezone is not specified, the current time zone is used

MAX

Returns the maximum value of expression across all input values

MD5

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

MIN

Returns the minimum value of expression across all input values

MOD

Returns the remainder of number_1/number_2

NEXTVAL

Advance sequence and return new value

NOW

Returns the current date and time (start of current transaction)

NULLIF

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1.

NUMRANGE

Creates a range of numerics. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

NUM_NONNULLS

Returns the number of non-null values

NUM_NULLS

Returns the number of null values

OCTET_LENGTH

Returns the number of bytes in string

OVERLAY

Overlays string with overlay_string, starting at start_position and for length characters.

PI

Returns the PI constant.

POWER

Returns number_1 raised to the power of number_2

QUOTE_IDENT

Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

QUOTE_LITERAL

Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.

QUOTE_NULLABLE

Returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled.

RADIANS

Converts degrees to radians

RANDOM

Returns a random value in the [0, 1] range.

RANGE_LOWER

Returns the lower bound of range

RANGE_MERGE

Returns the smallest range which includes both of the given ranges

RANGE_UPPER

Returns the upper bound of range

REGEXP_MATCH

Returns the captured substring(s) resulting from the first match of a POSIX regular expression to the string.

REGEXP_REPLACE

Replaces the substring(s) matching a POSIX regular expression.

REGEXP_SPLIT_TO_ARRAY

Splits the string using a POSIX regular expression as the delimiter.

REPEAT

Repeats string the specified number of times

REPLACE

Replaces all occurrences in string of substring from with substring to

REVERSE

Returns the reversed string.

RIGHT

Returns last n characters in the string. When n is negative, return all but first |n| characters.

ROUND

Rounds the number to the nearest integer or to int decimal places

RPAD

Fills up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated.

RTRIM

Removes the longest string containing only characters from characters (a space by default) from the end of string

SCALE

Returns the scale of the argument (the number of decimal digits in the fractional part)

SEM_BOOLEAN_TO_CHAR

Converts a boolean to its standard string representation.

SEM_CAST_BOOLEAN

Explicitly cast its argument as a boolean. This can help the database or JDBC driver to infer the type of a bound parameter.

SEM_CAST_NUMERIC

Explicitly cast its argument as a numeric value (decimal or integer). This can help the database or JDBC driver to infer the type of a bound parameter.

SEM_CAST_STRING

Explicitly cast its argument as a string (varchar). This can help the database or JDBC driver to infer the type of a bound parameter.

SEM_CAST_TIMESTAMP

Explicitly cast its argument as a timestamp. This can help the database or JDBC driver to infer the type of a bound parameter.

SEM_CAST_UUID

Explicitly cast its argument as a UUID. This can help the database or JDBC driver to infer the type of a bound parameter.

SEM_CONCAT

Returns a string concatenating the input values with the separator, optionally removing null values

SEM_EDIT_DISTANCE

Calculates the number of insertions, deletions or substitutions required to transform string1 into string2. If one or both of the strings are null the distance will be the largest integer value (2147483647).

SEM_EDIT_DISTANCE_SIMILARITY

Calculates the number of insertions, deletions or substitutions required to transform string1 into string2, and returns the Normalized value of the Edit Distance between two Strings. The value is between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0.

SEM_INSTR

Finds the location of a substring within a specified string.

SEM_JARO_WINKLER

Calculates the measure of agreement between two strings using Jaro-Winkler method. The value is between 0 (no match) and 1 (perfect match). If one or both strings are null the result will be 0.

SEM_JARO_WINKLER_SIMILARITY

Calculates the measure of agreement between two strings using Jaro-Winkler method, and returns a score between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0.

SEM_NGRAMS_SIMILARITY

Calculates the measure of agreement between two strings using the Dice’s coefficient similarity measure applied to the n-grams of the strings, and returns a score between 0 (no match) and 100 (perfect match). If one or both strings are null the result will be 0. The ngrams_length parameter defines the length of the n-grams (2 by default).

SEM_NORMALIZE

Returns a string with Latin (supplement, Extended-A and Extended-B) characters converted into their ASCII equivalents, and other (space and non-alphanumeric) characters eliminated.

SEM_NUMBER_TO_CHAR

Converts a number to its standard string representation.

SEM_SUBSTRING

Returns a portion of string, beginning at character position, substring_length characters long. If position is 0, then it is treated as 1. If position is positive, then the function counts from the beginning of string to find the first character. If position is negative, then the function counts backward from the end of string. If substring_length is omitted, then the function returns all characters to the end of string. Length is calculated using characters as defined by the input character set.

SEM_TIMESTAMP_TO_CHAR

Converts a timestamp to its standard string representation.

SEM_TO_CHAR

Converts a string or a number to its standard string representation.

SEM_UUID_TO_CHAR

Converts a UUID to its standard string representation.

SEQ_NEXTVAL

Get the next value of a sequence. Note that this function is not supported in Enrichers.

SETSEED

Sets the seed for subsequent random() calls (value between -1.0 and 1.0, inclusive)

SETVAL

Set sequence’s current value

SET_BIT

Set bit in string

SET_BYTE

Set byte in string

SIGN

Returns the sign of the argument (-1, 0, +1)

SIN

Returns the sine.

SPLIT_PART

Splits string on delimiter and return the element at position (counting from one)

SQRT

Returns the square root

STATEMENT_TIMESTAMP

Returns the current date and time (start of current statement)

STRING_AGG

Return input values concatenated into a string, separated by delimiter

STRPOS

Returns the location of specified substring in string

SUBSTR

Extracts a substring from string starting at from position and for count characters.

SUBSTRING_REGEX_PATTERN

Extracts from string a substring matching the pattern (a POSIX regular expression).

SUBSTRING_SQL_PATTERN

Extracts from string a substring matching an SQL regular expression.

SUM

Returns the sum of expression across all input values

TAN

Returns the tangent.

TIMEOFDAY

Returns the current date and time (like clock_timestamp, but as a text string)

TO_ASCII

Converts string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)

TO_CHAR

Convert the value (time stamp, interval, integer, real/double, numeric, string) to string according to the format.

TO_DATE

Converts the string to date

TO_HEX

Converts number to its equivalent hexadecimal representation

TO_NUMBER

Converts the string to numeric

TO_TIMESTAMP

Converts the string to time stamp

TRANSACTION_TIMESTAMP

Returns the current date and time (start of current transaction)

TRANSLATE

Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are removed.

TRUNC

Truncates the number toward zero or to int decimal places

TSRANGE

Creates a range of timestamps without time zone. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

TSTZRANGE

Creates a range of timestamps with time zone. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'.

UPPER

Converts string to upper case

UPPER_INC

Returns a boolean indicating whether the upper bound of the range is inclusive.

UPPER_INF

Returns a boolean indicating whether the upper bound of the range is infinite.

WIDTH_BUCKET

Returns the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2; returns 0 or count+1 for an input outside the range.

WIDTH_BUCKET_THRESHOLDS

Returns the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained.

XMLAGG

Returns the concatenation of XML values

Authoring Type

Batch Date

Batch ID

Batch Submitter

Class Name

Confidence Score

Confirmation Status

Constraint Name

Constraint Type

Created On

Created By

Deleted By

Deleted On

Delete Operation

Delete Path

Delete Type

Checkout Cause

Dups Operation ID

Error Status

Exclusion Group

From Batch ID

Golden Type

Has Override

Has Sugg. Merge

Is Confirmed

Load ID

Masters Count

Match Group ID

Old Match Group ID

Original Batch ID

Original Confidence Score

Original Confirmation Status

Original Exclusion Group

Original Masters Count

Publisher ID

Source ID

Sugg. Merge Confidence Score

Sugg. Merge ID

Sugg. Merge Masters Count

To Batch ID

Updated On

Updated By

View Type

Branch ID

From Edition

To Edition