The following table lists the built-in functions available in SemQL.
Function |
Description |
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 non-numeric 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 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. The function returns a value of the same type as the argument. |
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. |
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. |
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_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_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_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_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_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). |
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. |
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. |
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. |