SemQL Functions List
SemQL support an extensive list of built-in functions for PostgreSQL, Oracle and SQL Server.
The following tables lists the built-in functions available in SemQL.
Functions for Oracle
The following functions are available when using Oracle.
Function | Description |
---|---|
|
Returns the absolute value of number. |
|
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. |
|
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. |
|
Returns the decimal representation in the database character set of the first character of string. |
|
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. |
|
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. |
|
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. |
|
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). |
|
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. |
|
Computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer. |
|
Returns the smallest integer greater than or equal to number. |
|
Returns the character having the binary equivalent to number as a string value in the database character set. |
|
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. |
|
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. |
|
Returns string1 concatenated with string2. This function is equivalent to the concatenation operator. |
|
Converts a character string from one character set to another. |
|
Returns the cosine of number (an angle expressed in radians). |
|
Returns the hyperbolic cosine of number. |
|
Returns the current date in the session time zone, in a value in the Gregorian calendar. |
|
Returns the current date and time in the session time zone. If you omit precision, then the default is 6. |
|
Returns the value of the database time zone. The return type is a time zone offset (a character type in the format '+/- HH:MM') or a time zone region name. |
|
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. |
|
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. |
|
Returns e raised to the number-th power, where e = 2.71828183 … The function returns a value of the same type as the argument. |
|
Extracts and returns the day from expr. expr must be a valid ANSI date. |
|
Extracts and returns the hour from expr. expr must be a valid ANSI datetime. |
|
Extracts and returns the minute from expr. expr must be a valid ANSI datetime. |
|
Extracts and returns the month from expr. expr must be a valid ANSI date. |
|
Extracts and returns the second from expr. expr must be a valid ANSI datetime. |
|
Extracts and returns the abbreviation of the timezone from expr. expr must be a valid ANSI datetime including a timezone. |
|
Extracts and returns the hour of the timezone from expr. expr must be a valid ANSI datetime including a timezone. |
|
Extracts and returns the minute of the timezone from expr. expr must be a valid ANSI datetime including a timezone. |
|
Extracts and returns the region of the timezone from expr. expr must be a valid ANSI datetime including a timezone. |
|
Extracts and returns the year from expr. expr must be a valid ANSI date. |
|
Returns largest integer equal to or less than number. |
|
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. |
|
Returns the greatest of the list of one or more expressions. |
|
Converts string containing hexadecimal digits to a raw value. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
Returns the date of the last day of the month that contains date. |
|
Returns the least of the list of expressions. |
|
Returns the length of string. Length is calculated using characters as defined by the input character set. |
|
Returns the length of string. Length is calculated using characters as defined by the UC2 code point. |
|
Returns the length of string. Length is calculated using characters as defined by the UC4 code point. |
|
Returns the length of string. Length is calculated using bytes instead of characters. |
|
Returns the length of string. Length is calculated using Unicode complete characters. |
|
Returns the natural logarithm of number, where number is greater than 0. |
|
Returns the current date and time in the session time zone. |
|
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. |
|
Returns char, with all letters lowercase. |
|
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. |
|
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. |
|
Returns the remainder of number2 divided by number1. Returns number2 if number1 is 0. |
|
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. |
|
If number1 is not a number (NaN) then NANVL returns number2. Otherwise, it returns number1. |
|
Returns the character having the binary equivalent to number as a string value in the national character set. |
|
Returns the date and time (given in timezone 1) converted in time zone timezone2. |
|
Returns the date of the first weekday named by day_name that is later than the date date. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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 |
|
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'. |
|
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'. |
|
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. |
|
If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. |
|
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. |
|
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. |
|
Converts raw to a character value containing its hexadecimal equivalent. |
|
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. |
|
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. 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. |
|
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. 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. |
|
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. 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. |
|
Returns the remainder of number2 divided by number1. |
|
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. |
|
Returns date_or_number rounded to the unit specified by the format model fmt_or_integer. |
|
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. |
|
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. |
|
Converts a boolean to its standard string representation. The returned value is "true" or "false". |
|
Returns a string concatenating the input values with the separator. Set the skip_null parameter to '1' to skip null values. Set the sep parameter to an empty string '' to have no separator. |
|
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. |
|
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. |
|
Finds the location of a substring within a specified string. |
|
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. |
|
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. |
|
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). |
|
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. |
|
Converts a number to its standard string representation. |
|
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. |
|
Converts a timestamp to its standard string representation. |
|
Converts a string or a number to its standard string representation. |
|
Generic conversion to a string representation. |
|
Converts a UUID to its standard string representation. |
|
Get the next value of a sequence. Note that this function is not supported in Enrichers. |
|
Returns the sign of number. The sign is: -1 if n<0, 0 if n=0, 1 if n>0. |
|
Returns the sine of number (an angle expressed in radians). |
|
Returns the hyperbolic sine of number. |
|
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. Note that phonetization methods such as CAVERPHONE or METAPHONE for person names and METAPHONE or REFINEDSOUNDEX for organization names give better results for matching. These methods are available in the Text Normalization and Transliteration plugin. |
|
Returns the square root of number. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
Returns the current date and time set for the operating system on which the database resides. |
|
Returns the system date, including fractional seconds and time zone, of the system on which the database resides. |
|
Returns the value of parameter associated with the context namespace. |
|
Extracts the UTC (Coordinated Universal Time—formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name. |
|
Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. |
|
Returns the tangent of number (an angle expressed in radians). |
|
Returns the hyperbolic tangent of number. |
|
Returns a double-precision floating-point number. |
|
Returns a single-precision floating-point number. |
|
Converts expr to its string representation optionally using fmt and nlsparam for the conversion. |
|
Converts expr to a CLOB (large string) |
|
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. |
|
Converts a character string to an INTERVAL DAY TO SECOND value. |
|
Returns string with all of its single-byte characters converted to their corresponding multibyte characters. |
|
Converts expr to a number value using the optional format model fmt and nlsparam. |
|
Returns string with all of its multibyte characters converted to their corresponding single-byte characters. |
|
Converts string a timestamp value. The optional fmt specifies the format of string. |
|
Converts string to a TIMESTAMP WITH TIME ZONE value. The optional fmt specifies the format of string. |
|
Converts string to an INTERVAL YEAR TO MONTH type. |
|
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. |
|
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. |
|
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. |
|
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. |
|
Returns string with all letters uppercase. |
|
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. |
Functions for PostgreSQL
The following functions are available when using PostgreSQL.
Function | Description |
---|---|
|
Returns the absolute value |
|
Returns the inverse cosine |
|
Subtracts arguments, producing a symbolic result that uses years and months, rather than just days |
|
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 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. |
|
Returns the inverse sine |
|
Returns the inverse tangent |
|
Returns the inverse tangent of number_1/number_2 |
|
Returns the average (arithmetic mean) of all input values |
|
Returns the bitwise AND of all non-null input values, or null if none |
|
Returns the number of bits in string |
|
Returns the bitwise OR of all non-null input values, or null if none |
|
Returns true if all input values are true, otherwise false |
|
Returns true if at least one input value is true, otherwise false |
|
Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string |
|
Returns the cube root |
|
Returns the nearest integer greater than or equal to argument |
|
Returns the nearest integer greater than or equal to argument (same as ceil) |
|
Returns the number of characters in string |
|
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. |
|
Returns the current date and time (changes during statement execution) |
|
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. |
|
Concatenates the text representations of all the arguments. NULL arguments are ignored. |
|
Concatenates all but the first argument with separators. The first argument is used as the separator string. NULL arguments are ignored. |
|
Converts string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. |
|
Converts string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. |
|
Converts string to dest_encoding. |
|
Returns the cosine |
|
Returns the cotangent |
|
Returns the number of input rows for which the value of expression is not null |
|
Returns the current date |
|
Returns the current time of day |
|
Returns the current date and time (at the beginning of current the transaction) |
|
Return value most recently obtained with nextval for specified sequence |
|
Creates a range of dates. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'. |
|
Get subfield from a timestamp or an interval. The part to extract is defined by the text. |
|
Truncate timestamp or interval to the precision specified in the text. |
|
Decodes binary data from textual representation in string. Options for format are same as in encode. |
|
Converts radians to degrees |
|
Converts two strings to their Soundex codes and then reports the number of matching code positions. Since Soundex codes have four characters, the result ranges from zero to four, with zero being no match and four being an exact match. |
|
Returns the integer quotient of number_1/number_2 |
|
Returns a character string containing the phonetic representation of string using the Double Metaphone algorithm. This function returns the primary code for the string. See also: DMETAPHONE_ALT. |
|
Returns a character string containing the phonetic representation of string using the Double Metaphone algorithm. This function returns the secondary or alternate code for the string. See also: DMETAPHONE. |
|
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. |
|
Equivalent to bool_and |
|
Returns the exponential |
|
Returns the nearest integer less than or equal to argument |
|
Formats the arguments according to format_string. This function is similar to the C function sprintf. |
|
Extract bit from string |
|
Extract byte from string |
|
The GREATEST function selects the largest value from a list of any number of expressions. |
|
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. |
|
Creates a range of integers. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'. |
|
Creates a range of bigints. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'. |
|
Returns a boolean indicating whether the range empty. |
|
Tests for finite date, interval or timestamp (not +/-infinity) |
|
Aggregates values as a JSON array |
|
Aggregates name/value pairs as a JSON object |
|
Aggregates values as a JSON array |
|
Aggregates name/value pairs as a JSON object |
|
Adjust interval so 30-day time periods are represented as months |
|
Adjust interval so 24-hour time periods are represented as days |
|
Adjust interval using justify_days and justify_hours, with additional sign adjustments |
|
Return value most recently obtained with nextval for any sequence |
|
The LEAST function selects the smallest value from a list of any number of expressions. |
|
Returns the first n characters in the string. When n is negative, return all but last n characters. |
|
Returns the number of characters in string with an optional given encoding. The string must be valid in this encoding. |
|
Returns the Levenshtein distance between two strings, computed according to the cost specified for a character insertion, deletion, or substitution, respectively (you may set these values to 1). |
|
This function is an accelerated version of the LEVENSTHTEIN function that returns accurate values for actual distances smaller than max_distance. |
|
Returns the natural logarithm. |
|
Returns the current time of day |
|
Return the current date and time (at start of current transaction) |
|
Returns the logarithm in base 10. |
|
Convert string to lower case |
|
Returns a boolean indicating whether the lower bound of the range is inclusive. |
|
Returns a boolean indicating whether the lower bound of the range is infinite. |
|
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). |
|
Removes the longest string containing only characters from characters (a space by default) from the start of string |
|
Create date from integer year, month and day fields |
|
Create interval from years, months, weeks, days, hours, minutes and seconds fields. If a field is left empty, it defaults to zero. |
|
Create time from hour, minute and seconds fields |
|
Create timestamp from year, month, day, hour, minute and seconds fields |
|
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 |
|
Returns the maximum value of expression across all input values |
|
Calculates the MD5 hash of string, returning the result in hexadecimal |
|
Returns a character string containing the phonetic representation of string using the Metaphone algorithm, with a maximum length equal to the integer argument. |
|
Returns the minimum value of expression across all input values |
|
Returns the remainder of number_1/number_2 |
|
Advance sequence and return new value |
|
Returns the current date and time (start of current transaction) |
|
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. |
|
Creates a range of numerics. Inclusivity is a string with an opening/closing square bracket or parenthesis indicating inclusiveness, with a default of '(]'. |
|
Returns the number of non-null values |
|
Returns the number of null values |
|
Returns the number of bytes in string |
|
Overlays string with overlay_string, starting at start_position and for length characters. |
|
Returns the PI constant. |
|
Returns number_1 raised to the power of number_2 |
|
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. |
|
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. |
|
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. |
|
Converts degrees to radians |
|
Returns a random value in the [0, 1] range. |
|
Returns the lower bound of range |
|
Returns the smallest range which includes both of the given ranges |
|
Returns the upper bound of range |
|
Returns the captured substring(s) resulting from the first match of a POSIX regular expression to the string. flags contains one of more of the following values: i - Case-insensitive match, c - Case-sensitive match. |
|
Replaces the first substring matching a POSIX regular expression. flags contains one of more of the following values: i - Case-insensitive match, c - Case-sensitive match. To replace all substrings, add 'g' to the flags. |
|
Splits the string using a POSIX regular expression as the delimiter. flags contains one of more of the following values: i - Case-insensitive match, c - Case-sensitive match. |
|
Repeats string the specified number of times |
|
Replaces all occurrences in string of substring from with substring to |
|
Returns the reversed string. |
|
Returns last n characters in the string. When n is negative, return all but first |n| characters. |
|
Rounds the number to the nearest integer or to int decimal places |
|
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. |
|
Removes the longest string containing only characters from characters (a space by default) from the end of string |
|
Returns the scale of the argument (the number of decimal digits in the fractional part) |
|
Converts a boolean to its standard string representation. The returned value is "true" or "false". |
|
Explicitly cast its argument as a boolean. This can help the database or JDBC driver to infer the type of a bound parameter. |
|
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. |
|
Explicitly cast its argument as a string (varchar). This can help the database or JDBC driver to infer the type of a bound parameter. |
|
Explicitly cast its argument as a timestamp. This can help the database or JDBC driver to infer the type of a bound parameter. |
|
Explicitly cast its argument as a UUID. This can help the database or JDBC driver to infer the type of a bound parameter. |
|
Returns a string concatenating the input values with the separator. Set the skip_null parameter to '1' to skip null values. Set the sep parameter to an empty string '' to have no separator. |
|
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). |
|
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. |
|
Finds the location of a substring within a specified string. |
|
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). |
|
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. |
|
Converts a number to its standard string representation. |
|
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. |
|
Converts a timestamp to its standard string representation. |
|
Converts a string or a number to its standard string representation. |
|
Generic conversion to a string representation. |
|
Converts a UUID to its standard string representation. |
|
Get the next value of a sequence. Note that this function is not supported in Enrichers. |
|
Sets the seed for subsequent random() calls (value between -1.0 and 1.0, inclusive) |
|
Set sequence’s current value |
|
Set bit in string |
|
Set byte in string |
|
Returns the sign of the argument (-1, 0, +1) |
|
Returns the sine. |
|
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. Note that phonetization methods such as CAVERPHONE or METAPHONE for person names and METAPHONE or REFINEDSOUNDEX for organization names give better results for matching. These methods are available in the Text Normalization and Transliteration plugin. |
|
Splits string on delimiter and return the element at position (counting from one) |
|
Returns the square root |
|
Returns the current date and time (start of current statement) |
|
Return input values concatenated into a string, separated by delimiter |
|
Returns the location of specified substring in string |
|
Extracts a substring from string starting at from position and for count characters. |
|
Extracts from string a substring matching the pattern (a POSIX regular expression). |
|
Extracts from string a substring matching an SQL regular expression. |
|
Returns the sum of expression across all input values |
|
Returns the tangent. |
|
Returns the current date and time (like clock_timestamp, but as a text string) |
|
Converts string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings) |
|
Convert the value (time stamp, interval, integer, real/double, numeric, string) to string according to the format. See https://www.postgresql.org/docs/current/functions-formatting.html for more information about format patterns. |
|
Converts the string to date. See https://www.postgresql.org/docs/current/functions-formatting.html for more information about format patterns. |
|
Converts number to its equivalent hexadecimal representation |
|
Converts the string to numeric. See https://www.postgresql.org/docs/current/functions-formatting.html for more information about format patterns. |
|
Converts the string to time stamp. See https://www.postgresql.org/docs/current/functions-formatting.html for more information about format patterns. |
|
Returns the current date and time (start of current transaction) |
|
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. |
|
Truncates the number toward zero or to int decimal places |
|
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 '(]'. |
|
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 '(]'. |
|
Converts string to upper case |
|
Returns a boolean indicating whether the upper bound of the range is inclusive. |
|
Returns a boolean indicating whether the upper bound of the range is infinite. |
|
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. |
|
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. |
|
Returns the concatenation of XML values |
Functions for SQLServer
The following functions are available when using SQLServer.
Function | Description |
---|---|
|
A mathematical function that returns the absolute (positive) value of the specified numeric expression. |
|
A function that returns the angle, in radians, whose cosine is the specified float expression. |
|
This function returns the approximate number of unique non-null values in a group. |
|
Returns the ASCII code value of the leftmost character of a character expression. |
|
A function that returns the angle, in radians, whose sine is the specified float expression. |
|
A function that returns the angle, in radians, whose tangent is a specified expression. |
|
Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. |
|
This function returns the average of the values in a group. It ignores null values. |
|
This function returns the smallest integer greater than, or equal to, the specified numeric expression. |
|
This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found. |
|
This function returns the checksum of the values in a group. |
|
Returns the item at the specified index from a list of values in SQL Server. |
|
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. |
|
This function concatenates two or more strings together. |
|
A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. |
|
A mathematical function that returns the trigonometric cotangent of the specified angle - in radians - in the specified float expression. |
|
Returns the number of input rows for which the value of expression is not null. |
|
This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value. Note that datepart must be one of the following values, provided between quotes (e.g.: 'year'): year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond. |
|
This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate. Note that datepart must be one of the following values, provided between quotes (e.g.: 'year'): year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond. |
|
This function returns a date value that maps to the specified year, month, and day values. |
|
This function returns a character string representing the specified datepart of the specified date. Note that datepart must be one of the following values, provided between quotes (e.g.: 'year'): year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond. |
|
This function returns an integer representing the specified datepart of the specified date. Note that datepart must be one of the following values, provided between quotes (e.g.: 'year'): year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond. |
|
This function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument. |
|
This function returns an integer that represents the day (day of the month) of the specified date. |
|
This function returns the corresponding angle, in degrees, for an angle specified in radians. |
|
This function returns an integer value measuring the difference between the SOUNDEX values of two different character expressions. |
|
This function returns the last day of the month containing a specified date, with an optional offset. |
|
Returns the exponential value of the specified float expression. |
|
Returns the largest integer less than or equal to the specified numeric expression. |
|
Returns a value formatted with the specified format and optional culture in SQL Server 2017. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT. |
|
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. |
|
Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server. |
|
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. |
|
Replaces NULL with the specified replacement value. |
|
Returns the left part of a character string with the specified number of characters. |
|
Returns the number of characters of the specified string expression, excluding trailing blanks. |
|
Returns the length of string. Length is calculated using characters as defined by the input character set. |
|
Returns the natural logarithm of the specified float expression. |
|
Returns the base-10 logarithm of the specified float expression. |
|
Returns a character expression after converting uppercase character data to lowercase. |
|
Returns the string, 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. |
|
Returns a character expression after it removes leading blanks. |
|
Returns the maximum value in the expression. |
|
Returns the minimum value in the expression. |
|
Returns an integer that represents the month of the specified date. |
|
Returns the Unicode character with the specified integer code, as defined by the Unicode standard. |
|
Creates a unique value of type uniqueidentifier. |
|
Returns a null value if the two specified expressions are equal. |
|
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. |
|
Returns the constant value of PI. |
|
Returns the value of the specified expression to the specified power. |
|
Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. |
|
Returns radians when a numeric expression, in degrees, is entered. |
|
Returns a pseudo-random float value from 0 through 1, exclusive. |
|
Replaces all occurrences of a specified string value with another string value. |
|
Repeats a string value a specified number of times. |
|
Returns the reverse order of a string value. |
|
Returns the right part of a character string with the specified number of characters. |
|
Returns a numeric value, rounded to the specified length or precision. |
|
Returns a character string after truncating all trailing spaces. |
|
Converts a boolean to its standard string representation. The returned value is "true" or "false". |
|
Returns a string concatenating the input values with the separator. Set the skip_null parameter to '1' to skip null values. Set the sep parameter to an empty string '' to have no separator. |
|
Converts a timestamp to its standard string representation. |
|
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). |
|
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. |
|
Finds the location of a substring within a specified string. |
|
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). |
|
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. |
|
Converts a number to its standard string representation. |
|
Returns part of a character, binary, text, or image expression. |
|
Converts a timestamp to its standard string representation. |
|
Converts a string or a number to its standard string representation. |
|
Generic conversion to a string representation. |
|
Converts a UUID to its standard string representation. |
|
Get the next value of a sequence. Note that this function is not supported in Enrichers. |
|
Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. |
|
Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression. |
|
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. |
|
Returns a string of repeated spaces. |
|
Returns the square root of the specified float value. |
|
Returns the square of the specified float value. |
|
Returns the statistical standard deviation of all values in the specified expression. |
|
Returns the statistical standard deviation for the population for all values in the specified expression. |
|
Returns character data converted from numeric data. |
|
Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. |
|
Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function. |
|
Splits the character expression using specified separator. |
|
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. |
|
Returns part of a character, binary, text, or image expression in SQL Server. |
|
Returns the sum of all the values, or only the DISTINCT values, in the expression. |
|
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. |
|
Returns the tangent of the input expression. |
|
Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters. |
|
Removes the characters (by default a space) from the start or end of the string expression. |
|
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. |
|
Returns a character expression with lowercase character data converted to uppercase. |
|
Returns the statistical variance of all values in the specified expression. |
|
Returns the statistical variance for the population for all values in the specified expression. |
|
Returns an integer that represents the year of the specified date. |