Function Attribute Properties

The following initial interface is displayed when creating a function attribute.

The interface is used to select the function upon which the attribute is to be based.

If creating a function attribute at an RPI client in which one or more auxiliary databases has been configured, you must first choose the Database in respect of which to create the function attribute.

Choose the database… is not displayed if creating a function attribute at a client at which no auxiliary databases have been configured.

The list of functions available to you when building a function attribute differs based on the type of database in respect of which the attribute is to be created.

SQL Server/SQL Server PDW

Function Name	Description
Date & Time Functions
Date Add(Date Part, Number of Date Parts to Add, Date)	Returns a new datetime value based on adding an interval to the specified date.
Date Difference(Date Part, Start Date, End Date)	Returns the number of date and time boundaries crossed between two specified dates.
Date Part(Date Part, Date)	Returns an integer representing the specified date part of the specified date.
Day(Date)	Returns an integer representing the day date part of the specified date.
Month(Date)	Returns an integer representing the month date part of the specified date.
To string (Date)	Converts a date or datetime value into a string in accordance with the specified format (see http://msdn.microsoft.com/en-us/library/ms187928.aspx for a list of valid formats).
Year(Date)	Returns an integer representing the year date part of the specified date.
Mathematical Functions
Absolute(Number)	Returns the absolute, positive value of the given numeric expression.
Ceiling(Number)	Returns the smallest integer greater than, or equal to, the given numeric expression.
Exponential(Number)	Returns the exponential value of the given float expression.
Floor(Number)	Returns the largest integer less than or equal to the given numeric expression.
Random(Number, Number)	Returns a random integer value
String Functions
Difference(String, String)	Returns the difference between the SOUNDEX values of two character expressions as an integer.
Left(String, Number of Characters)	Returns the left part of a character string with the specified number of characters.
Left trim(String)	Returns a character expression after removing leading blanks.
Length(String)	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case(String)	Returns a character expression after converting uppercase character data to lowercase.
Replace(Original String, Start Position, Length, String to Insert)	Deletes a specified length of characters and inserts another set of characters at a specified starting point.
Reverse string(String)	Returns the reverse of a character expression.
Right(String, Number of Characters)n	Returns the right part of a character string with the specified number of characters.
Right trim(String)	Returns a character string after truncating all trailing blanks.
String concatenation (2 strings)	Concatenates two string values
String concatenation (3 strings)	Concatenates three string values
String concatenation (4 strings)	Concatenates four string values
String concatenation (5 strings)	Concatenates five string values
SubString(String, Start Position, Length)	Returns part of a character, binary, text, or image expression.

Netezza

Function Name	Description
Date & Time Functions
Date part	Returns an integer representing the specified datepart of the specified date
Date difference	Returns the interval between two dates
Add months	Adds a number of months on to date
Months between	Returns the number of months between two dates
To timestamp	Converts an attribute value into a timestamp type
To date	Converts an attribute value into a date type
To time	Converts an attribute value into a time type
To time2	Converts an attribute value into a time type
To interval	Converts an attribute value into interval type
Mathematical Functions
Absolute(Number)	Returns the absolute, positive value of the given numeric expression.
Ceiling(Number)	Returns the smallest integer greater than, or equal to, the given numeric expression.
Exponential(Number)	Returns the exponential value of the given float expression.
Floor(Number)	Returns the largest integer less than or equal to the given numeric expression.
To numeric	Converts an attribute value into a numeric type
To numeric2	Converts an attribute value into a numeric type
To numeric3	Converts an attribute value into a numeric type
To real	Converts an attribute value into a real type
To double	Converts an attribute value into a double type
To Boolean	Converts an attribute value into a Boolean type
To integer	Converts an attribute value into an integer type
String Functions
Length	Returns the length of the specified string
Lower case	Returns a character expression after converting upper case character data to lower case
Upper case	Returns a character expression after converting lower case character data to upper case
Capitalize	Returns a character expression after capitalizing the initial letter
Trim blanks	Returns a character expression after removing leading and trailing blanks
Left trim blanks	Returns a character expression after removing leading blanks
Right trim blanks	Returns a character string after truncating all trailing blanks
Trim characters	Returns a character expression after removing leading and trailing characters
Left trim characters	Returns a character expression after removing leading characters
Right trim characters	Returns a character string after truncating all trailing characters
Left pads	Returns a character string of the desired length after padding the existing string to the left
Right pads	Returns a character string of the desired length after padding the existing string to the right
SubString	Returns part of a character, binary, text, or image expression
Position	Returns the location of a substring in a string
String concatenation (2 strings)	Concatenates two string values
String concatenation (3 strings)	Concatenates three string values
String concatenation (4 strings)	Concatenates four string values
String concatenation (5 strings)	Concatenates five string values
To string (char)	Converts an attribute value into a string type
To string (char(n))	Converts an attribute value into a string type
To string (Unicode char)	Converts an attribute value into a Unicode string type
To string (Unicode char(n))	Converts an attribute value into a Unicode string type
To string (varchar(n))	Converts an attribute value into a string type
To string (Unicode varchar(n))	Converts an attribute value into a Unicode string type

Oracle

Function Name	Description
Date & Time Functions
Date add	Returns a new datetime value based on adding an interval to the specified date
Mathematical Functions
Absolute(Number)	Returns the absolute, positive value of the given numeric expression.
Ceiling(Number)	Returns the smallest integer greater than, or equal to, the given numeric expression.
Exponential(Number)	Returns the exponential value of the given float expression.
Floor(Number)	Returns the largest integer less than or equal to the given numeric expression.
String Functions
Left(String, Number of Characters)	Returns the left part of a character string with the specified number of characters.
Left trim(String)	Returns a character expression after removing leading blanks.
Length(String)	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case(String)	Returns a character expression after converting uppercase character data to lowercase.
Reverse string(String)	Returns the reverse of a character expression.
Right(String, Number of Characters)n	Returns the right part of a character string with the specified number of characters.
Right trim(String)	Returns a character string after truncating all trailing blanks.

Teradata

Date & Time Functions
Year	Returns an integer representing the year datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Day	Returns an integer representing the day datepart of the specified date
Date Part	The part of date (a date or time value) for which an integer will be returned
Date Difference	Returns the number of date and time boundaries crossed between two specified dates
Mathematical Functions
Absolute	Returns the absolute value of an argument
Exponential	Returns the power value of an argument
Natural Log	Returns the natural logarithm of an argument
Log10	Returns the base 10 logarithm of an argument
Square Root	Returns the square root value of an argument
Ceiling	Returns the ceiling value of an argument
Round	Returns the rounding value of an argument
String Functions
Length	Returns the length of input text
Lower	Returns a character string identical to the input text, except that all uppercase letters are replaced by their lowercase equivalents
Upper	Returns a character string identical to the input text, except that all lowercase letters are replaced by their uppercase equivalents
Trim	Returns the trimmed input text
Index	Returns the position in string1 where string2 starts
Position	Returns the position in string2 where string1 starts
Right	Returns the text from the right position of the input text
Left	Length of characters to be extracted from the left position of input text
Substring	The length of the text to be extracted

GreenPlum Database

Function Name	Description
Date & Time Functions
Date Difference	The part of the start date and end date that specifies the type of boundary crossed
String Functions
Capitalize	Returns a character expression after capitalizing the initial letter
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Upper case	Returns a character expression after converting lower case character data to upper case

MySQL

Function Name	Description
Date & Time Functions
Add days	Adds a number of days to date
Add months	Adds a number of months to date
Date Add	Adds an interval to a date
Date Difference	Returns the interval between two dates
Mathematical Functions
Absolute	Return the absolute value
Ceiling	Return the smallest integer value not less than the argument
Exponential	Raise to the power of
Floor	Return the largest integer value not greater than the argument
String Functions
Left	Returns a character expression after removing leading blanks
Left pads	Returns a character string of the desired length after padding the existing string to the left
Left trim blanks	Returns a character expression after removing leading blanks
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Position	Returns the location of a substring in a string
Right	Returns the rightmost number of characters as specified
Right pads	Returns a character string of the desired length after padding the existing string to the right
Right trim blanks	Returns a character expression after removing trailing blanks
SubString	Returns part of a character, binary, text, or image expression
Trim blanks	Returns a character expression after removing trailing blanks
Upper case	Returns a character expression after converting lower case character data to upper case

AWS RedShift

Function Name	Description
Date & Time Functions
Add Months	Adds the specified number of months to a given date expression
Date Compare	Returns an integer. 0 if identical, -1 if first date is less than second date & 1 if first date is greater than second date
DateTime Compare Date	Returns an integer. 0 if identical, -1 if datetime is less than date and 1 if datetime is greater than date
Date Compare DateTime	Returns an integer. 0 if identical, -1 if date is less than datetime and 1 if first date is greater than datetime
Year	Returns the year of the specified date expression
Date Add	Returns the datetime
Date Difference	Returns the difference between datetime expressions
Date Part	Returns decimal number represents the datepart
Date Truncate	Returns datetime
Extract	Returns the datepart
Interval Compare	Returns an integer. 0 if identical, -1 if first interval is less than second interval and 1 if first interval is greater than second interval
Isfinite DateTime	Returns Boolean
Mathematical Functions
Absolute	Returns the absolute value of the specified numeric expression
Ceiling	Returns the ceiling value of the specified numeric expression
Floor	Returns the ceiling value of the specified numeric expression
Exponent	Returns the exponential value of the specified numeric expression
Power	Returns the exponential value of the specified numeric expression raised by the second numeric expression
Round	Rounds numbers to the nearest integer or decimal
String Functions
Length	Returns the length of the specified text
Quote Identifier	Returns equivalent quoted identifier
Quote Literal	Returns equivalent quoted literal
Repeat	Repeats text the specified number of times
Replace	Returns all occurrences of the replaced text
Right Trim	Returns the trimmed text
Reverse	Returns the reversed text
String Position	Returns integer representing the position of the second text in the first text expression
Substring	Returns the extracted text from the specified text expression
Trim	Returns the trimmed text

Sybase IQ

Function Name	Description
Date & Time Functions
Date Part	Returns the value of part of a datetime value
Add Months	Adds a number of months on to date
Months Between	Returns the number of months between two dates
String Functions
Length	Returns the number of characters in a string. Trailing white space characters are included in the length returned.
Lower Case	Returns a character expression after converting upper case character data to lower case
Upper Case	Returns a character expression after converting upper case character data to upper case
Trim Blanks	Removes leading and trailing blanks from a string
Left Trim blanks	Returns a character expression after removing leading blanks
Right Trim blanks	Returns a character string after truncating all trailing blanks
Left Trim characters	Returns a character expression after removing leading characters
Left Pads	Returns a character string of the desired length after padding the existing string to the left
Right Pads	Returns a character string of the desired length after padding the existing string to the right
Substring	Returns part of a character, binary, text, or image expression
Position	Returns the location of a substring in a string
Math Functions
Absolute	Returns the absolute value of the given numeric expression
Exponential	Returns the exponential value of the given float expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Floor	Returns the largest integer less than or equal to the given numeric expression

PostgreSQL

Function Name	Description
Date & Time Functions
Date truncate	Returns a truncated timestamp to a specified precision
Extract	Returns the extracted timestamp
Date Part	Returns the date part of the given date and time expression
Year	Returns the Year part of the given date and time expression
Month	Returns the Month part of the given date and time expression
Day	Returns the day part of the given date and time expression
String Functions
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Upper case	Returns a character expression after converting lower case character data to upper case
InitCap	Returns a character expression after capitalizing the initial letter
Left	Returns the left part of a character string with the specified number of characters
Right	Returns the right part of a character string with the specified number of characters
Right trim	Remove all characters from right position of a specified character expression
Left trim	Remove all characters from left position of a specified character expression
Substring	Returns a character expression based on the specified starting position and length
Concatenate	Concatenates string expressions
Math Functions
Absolute	Returns the absolute (positive) value
Floor	Returns the largest integer not greater than given argument
Ceiling	Returns the smallest integer not less than the given argument
Power	Returns the exponential value of a numeric expression raised to the specified power
Exp	Returns e raised to the power of the specified number

DB2

Function Name	Description
Date & Time Functions
Date Difference	Returns the difference between two timestamps
Add Days	Adds a number of days to a given datetime expression
Add Months	Adds a number of months to a given datetime expression
Add years	Adds a number of years to a given datetime expression
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date
String Functions
Length	Returns the number of characters
Left	Returns the left part of a character string with the specified number of characters
Right	Returns the right part of a character string with the specified number of characters
Lower case	Returns a character expression in which all the characters have been converted to lower case characters
Upper case	Returns a character expression in which all the characters have been converted to upper case characters
Left trim	Remove all characters from left position of a specified character expression
Right trim	Remove all characters from right position of a specified character expression
Replace	Replaces all found characters with specified source string
Substring	Returns a character expression based on the specified starting position and length
Locate in string	Returns the position of a character expression
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Exponential	Returns the exponential value of the given float expression
Power	Returns the base to the exponent power
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Floor	Returns the largest integer less than or equal to the given numeric expression

Splice Machine

Function Name	Description
Date & Time Functions
Add Months	Adds months
Month Between	Returns the number of months between two dates
Date Difference	Returns the difference between two timestamps, in terms of the specified interval
Date Add	Adds the value of an interval to a timestamp value and returns the sum as a new timestamp
Day	Returns the day part of the given date value
Last Day	Returns the date of the last day of the month
Next Day	Returns the date of the next specified day of the week
Minute	Returns the minute part
Minute2	Returns the minute part
Month	Returns the month part
Year	Returns the year part
String Functions
Trim	Returns the string resulting from trimming spaces from both sides
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Right Trim	Returns the string resulting from trimming spaces from right-hand side
Length	Returns the length of the string
Lowercase	Returns the lowercase equivalent of the string
Uppercase	Returns the uppercase equivalent of the string
InitCap	Converts the first letter of each word in a string to uppercase, and converts any remaining characters in each word to lowercase
Instr	Returns the index of the first occurrence of a substring in a string
Substring	Extracts and returns a portion of a character string
Locate	Used to search for a string
Concatenate	Concatenates string expressions
Replace	Replaces all occurrences of a substring within a string and returns the new string
Math Functions
Absolute	Returns the absolute value
Exp	Returns e raised to the power of the specified number
Ceiling	Returns the smallest number that is greater than or equal to the specified number
Floor	Returns the largest number that is less than or equal to the specified number
Modulus	Returns the remainder (modulus)
Pi	Returns PI

Hive

Function Name	Description
Date & Time Functions
Year	Returns the year part of a date or timestamp
Month	Returns the month part of a date or timestamp
Day of month	Returns the day part of a date or timestamp
Week of year	Returns the week number of a timestamp
Hour	Returns the hour part of a timestamp
Minute	Returns the minute part of a timestamp
Second	Returns the second part of a timestamp
DateAdd	Adds number of days to start date
DateSub	Subtract number of days to start date
Date difference	Returns the number of date boundaries crossed between two specified dates
Unix Timestamp	Converts date to Unix timestamp
String Functions
Length	Returns the length of the string
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Right Trim	Returns the string resulting from trimming spaces from right-hand side
Trim	Returns the string resulting from trimming spaces from both sides
Reverse	Returns the reversed string
Uppercase	Returns the uppercase equivalent of the string
Lowercase	Returns the lowercase equivalent of the string
Space	Returns the number of spaces generated
Repeat	Returns the repeated string
Concatenate	Combines first and second string values
Concatenate with separator	Combines first and second string values to specified separator
Left padding	Returns left-padded string
Right padding	Returns right-padded string
Substring	Returns the extracted string
Substring2	Returns the extracted string
Instring	Returns the position of the searched string
Locate	Returns the position of the searched string
Math Functions
Absolute	Returns the absolute value
Floor	Returns the maximum value that is <= to the evaluated value
Ceil	Returns the maximum value that is >= to the evaluated value
Random	Returns random number based on the specified seed
Exponential	Returns the exponential value of the evaluated value
Round	Returns the rounded value
Round2	Returns the rounded value
Power	Returns the power of the base value

MongoDB

Function Name	Description
Date & Time Functions
Date difference	Returns the number of date and time boundaries crossed between two specified dates
Current time	Returns the current time
Day name	Returns the name of the day
Day of month	Returns the Day of the month
Day of week	Returns the Day of the week
Day of year	Returns the Day of the year
Year	Returns the year part of a date or timestamp
Hour	Returns the Hour portion of the timestamp
Minute	Returns the Minute portion of the timestamp
Second	Returns the Second portion of the timestamp
Quarter	Returns the quarter number of the timestamp
Week	Returns the week number of the timestamp
Extract	Returns the extracted field portion of the source timestamp
String Functions
Length	Returns the length of the string
Right Trim	Returns the string resulting from trimming spaces from right-hand side
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Lowercase	Returns the lowercase equivalent of the string
Uppercase	Returns the uppercase equivalent of the string
Soundex	Returns a character string containing the phonetic representation of the string
Space	Returns the number of spaces generated
Concatenate	Combines first and second string values
Left	Returns the leftmost count of characters of the string
Right	Returns the rightmost count of characters of the string
Repeat	Returns the repeated string
Locate	Returns the starting position of the first occurrence of the searched string
Position	Returns the position of the searched string
Replace	Returns the replaced equivalent string
Substring	Returns the extracted string
Replace	Returns the inserted string
Math Functions
Absolute	Returns the absolute value
Floor	Returns the maximum value that is <= to the evaluated value
Ceil	Returns the maximum value that is >= to the evaluated value
Exponential	Returns the exponential value of the evaluated value
Round	Returns the rounded value
Power	Returns the power of the base value
Mod	Returns the remainder (Modulus)

SparkSQL

Function Name	Description
Date & Time Functions
Year	Returns the year part of a date or timestamp
Month	Returns the month part of a date or timestamp
Day of month	Returns the day part of a date or timestamp
Week of year	Returns the week number of a timestamp
Hour	Returns the hour part of a timestamp
Minute	Returns the minute part of a timestamp
Second	Returns the second part of a timestamp
DateAdd	Adds number of days to start date
DateSub	Subtract number of days to start date
Date difference	Returns the number of date boundaries crossed between two specified dates
Unix Timestamp	Converts date to Unix timestamp
String Functions
Length	Returns the length of the string
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Right Trim	Returns the string resulting from trimming spaces from right-hand side
Trim	Returns the string resulting from trimming spaces from both sides
Reverse	Returns the reversed string
Uppercase	Returns the uppercase equivalent of the string
Lowercase	Returns the lowercase equivalent of the string
Space	Returns the number of spaces generated
Repeat	Returns the repeated string
Concatenate	Combines first and second string values
Concatenate with separator	Combines first and second string values to specified separator
Left padding	Returns left-padded string
Right padding	Returns right-padded string
Substring	Returns the extracted string
Substring2	Returns the extracted string
Instring	Returns the position of the searched string
Locate	Returns the position of the searched string
Math Functions
Absolute	Returns the absolute value
Floor	Returns the maximum value that is <= to the evaluated value
Ceil	Returns the maximum value that is >= to the evaluated value
Random	Returns random number based on the specified seed
Exponential	Returns the exponential value of the evaluated value
Round	Returns the rounded value
Round2	Returns the rounded value
Power	Returns the power of the base value

Salesforce.com Database

Function Name	Description
Date & Time Functions
Current Date	Returns the current date
Current Time	Returns the current time
Date Difference	Returns the count of units of time elapsed from start date to end date
Hour	Returns the Hour portion of the timestamp
Minute	Returns the Minute portion of the timestamp
Month	Returns the Month portion of the timestamp
Month Name	Returns the Month name of the timestamp
Second	Returns the Second portion of the timestamp
Week	Returns the week number of the timestamp
Year	Returns the year part of a date or timestamp
Current Timestamp	Returns the current timestamp
String Functions
Concat	Returns the string that results from concatenating two strings
Difference	Returns an integer value from 0 to 4 that indicates the difference between the values returned by the SOUNDEX function for S1 and S2. A value of 4 indicates that S1 and S2 are the same, while a value of 0 indicates that the values have no similarity
Left	Returns the leftmost count of characters of the string. If the string requires double quoting, use SUBSTRING( ) instead
Length	Returns the number of characters in the string
Left Trim	Removes all leading blanks in the string
Replace	Returns first string with all occurrences of second string replaced with third string
Right	Returns the right-most count of characters of the string
Right Trim	Removes all trailing spaces in the string
Substring	Returns the substring starting at start (1=left) with length (count)
Ucase	Converts string to uppercase
Lowercase	Converts string to lowercase
Uppercase	Converts string to uppercase
Math Functions
Absolute	Returns the absolute value of a double value
Ceiling	Returns the smallest integer that is not less than the value
Floor	Returns the largest integer that is not greater than the value
Power	Returns a raised to the power of b
Random	Returns a random number x bigger or equal to 0.0 and smaller than 1.0

Azure SQL Database

Function Name	Description
Date & Time Functions
Date add	Returns a new datetime value based on adding an interval to the specified date
Date difference	Returns the number of date and time boundaries crossed between two specified dates
Date part	Returns an integer representing the specified datepart of the specified date
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
To string	Converts a date or datetime value into a string in accordance with the specified format
Year	Returns an integer representing the year datepart of the specified date
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Exponential	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression
Random	Returns a random integer value
String Functions
Difference	Returns the difference between the SOUNDEX values of two character expressions as an integer
Left	Returns the left part of a character string with the specified number of characters
Left trim	Returns a character expression after removing blanks
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Replace	Deletes a specified length of characters and inserts another set of characters at a specified starting point
Reverse string	Returns the reverse of a character expression
Right	Returns the right part of a character string with the specified number of characters
Right trim	Returns a character string after truncating all trailing blanks
String concatenation (2 strings)	Concatenates two string values
String concatenation (3 strings)	Concatenates three string values
String concatenation (4 strings)	Concatenates four string values
String concatenation (5 strings)	Concatenates five string values
SubString	Returns part of a character, binary, text, or image expression
Upper case	Returns a character expression after converting lower case character data to upper case

Vertica

Function Name	Description
Date & Time Functions
Date Difference	Returns the difference of start date and end date
Date Difference 2	Returns the difference of start time and end time
Add Months	Returns an added date and timestamp
Date Part	Returns the date part of the given date and time expression
Year	Returns the Year part of the given date and time expression
Month	Returns the Month part of the given date and time expression
Day	Returns the day part of the given date and time expression
String Functions
Length	Returns the length of the string
Lower case	Returns the lower case equivalent of the string
Upper case	Returns the upper case equivalent of the string
InitCap	Returns a capitalize first letter of the string and puts the rest in lowercase
Left	Returns the specified characters from the left side of the string
Right	Returns the specified characters from the right side of the string
Right trim	Returns the string resulting from trimming spaces from the right-hand side
Left trim	Returns the string resulting from trimming spaces from the left-hand side
Trim	Returns the string resulting from trimming spaces from both sides
Substring	Returns a value representing a substring of the specified string at the given position, given a value, a position, and an optional length
Concat	Concatenates string expressions
Replace	Replaces all occurrences of a substring within a string and returns with the new string
RPad	Returns string value representing a specific length filled on the right with specific characters
LPad	Returns string value representing a specific length filled on the left with specific characters
Split	Splits a string and returns a location of the beginning of the field
Math Functions
Absolute	Returns the absolute (positive) value
Floor	Rounds the returned value down to the next whole number
Ceiling	Rounds the returned value up to the next whole number
Modulus	Returns the remainder of a division operation
Exponential	Returns e raised to the power of the specified number

MarkLogic

Function Name	Description
Date & Time Functions
Year	Returns the added date and time
Month	Returns the month part of date and time expression
Day	Returns the day part of date and time expression
Date Add	Returns the added date and time
Date difference	Returns the difference of start date and end date
Date Part	Returns part of date and time expression
String Functions
Length	Returns the length of the character(s)
Left Trim	Removes leading spaces from a character expression
Right Trim	Removes the trailing spaces from a character expression
Upper	Returns the uppercase characters
Lower	Returns the lowercase characters
Left	Returns leftmost characters
Right	Returns rightmost characters
Substring	Returns a character string
Charindex	Returns the position of the character(s) to be found
Replace	Replaces all occurrences of specified character string value to another string value
Math Functions
Absolute	Returns the absolute (positive) value
Floor	Returns the largest integer smaller than the given numeric expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Power	Returns the exponential value of a numeric expression raised to the specified power

MariaDB

Function Name	Description
Date & Time Functions
Add days	Returns a new datetime value based on adding a day interval to the specified date
Add months	Returns a new datetime value based on adding a month interval to the specified date
Date Add	Returns a new datetime value based on adding an interval to the specified date
Date difference	Returns the number of day interval between two specified dates
String Functions
Left	Return the leftmost number of characters as specified
Left pads	Returns a character string of the desired length after padding the existing string to the left
Left trim blanks	Returns a character expression after removing leading blanks
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Position	Returns the location of a substring in a string
Right	Return the rightmost number of characters as specified
Right pads	Returns a character string of the desired length after padding the existing string to the right
Right trim blanks	Returns a character expression after removing trailing blanks
Substring	Returns parts of a character, binary, text, or image expression
Trim blanks	Returns a character expression after removing leading and trailing blanks
Upper case	Returns a character expression after converting lower case character data to upper case
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater that, or equal to, the given numeric expression
Exponential	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression
Random	Returns a random integer value

SAP HANA

Function Name	Description
Date & Time Functions
Day Of Year	Returns an integer representing the day datepart of the specified date
Week	Returns an integer representing the week datepart of the specified date
Week Day	Returns an integer representing the week day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date
Days between	Returns an integer representing the number of days between two dates
String Functions
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Left	Returns the left part of a character string with the specified number of characters
Right	Returns the right part of a character string with the specified number of characters
Lower case	Converts all characters in string to lowercase
Upper case	Converts all characters in string to uppercase
Left trim	Returns string trimmed of all leading spaces
Right trim	Returns string trimmed of all trailing spaces
SubString	Returns part of a character, binary, text, or image expression
Replace	Replaces a substring in a string with a new value
Concatenate	Concatenates two string values
Math Functions
Absolute	Returns the absolute value of the numeric argument 'n'
Ceiling	Returns the first integer that is greater than or equal to the value of 'n'
Floor	Returns the largest integer that is not greater than the numeric argument n
Power	Calculates the base number b raised to the power of an exponent e
Random	Returns a pseudo-random value in the range of 0 to less than 1.0

Azure Cosmos DB

Function Name	Description
Date & Time Functions
Date Part	Returns an integer representing the specified datepart of the specified date
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date
String Functions
Concatenate	Combines first and second string values
Left	Returns the leftmost count of characters of the string
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Length	Returns the length of the string
Lowercase	Returns the lowercase equivalent of the string
Replace	Returns the replaced equivalent string
Right	Returns the rightmost count of characters of the string
Right Trim	Returns the string resulting from trimming spaces from right-hand side
Substring	Returns the extracted string
Uppercase	Returns the uppercase equivalent of the string
Math Functions
Absolute	Returns the absolute value
Ceiling	Returns the maximum value that is >= to the evaluated value
Exponential	Returns the exponential value of the evaluated value
Floor	Returns the maximum value that is <= to the evaluated value

Azure Database for MySQL

Function Name	Description
Date & Time Functions
Add days	Adds a number of days to date
Add months	Adds a number of months to date
Date Add	Adds an interval to a date
Date difference	Returns the interval between two dates
String Functions
Left	Return the leftmost number of characters as specified
Left pads	Returns a character string of the desired length after padding the existing string to the left
Left trim blanks	Returns a character expression after removing leading blanks
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Position	Returns the location of a substring in a string
Right	Return the rightmost number of characters as specified
Right pads	Returns a character string of the desired length after padding the existing string to the right
Right trim blanks	Returns a character expression after removing trailing blanks
Substring	Returns parts of a character, binary, text, or image expression
Trim blanks	Returns a character expression after removing leading and trailing blanks
Upper case	Returns a character expression after converting lower case character data to upper case
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater that, or equal to, the given numeric expression
Exponential	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression
Random	Returns a random integer value

Azure Database for PostgreSQL

Function Name	Description
Date & Time Functions
Date Part	Return the part of a data and time
String Functions
Length	Returns the number of characters in string
Position	Return the location of a substring within a specified string
Left	Return the n number of characters specified in the argument from the left of a given string
Right	Return the n number of characters specified in the argument from the right of a given string
Substring	Returns the location of a substring in a string
Lower	Return the string to lower case
Upper	Return the string to upper case
Trim	Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string
Ltrim	Remove the longest string containing only characters from characters (a space by default) from the start of string
Rtrim	Remove the longest string containing only characters from characters (a space by default) from the end of string
Replace	Replace all occurrences in string of substring from with substring to
Strpos	Location of specified substring (same as position (substring in string), but note the reversed argument order)
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceil	Return the nearest integer greater than or equal to argument
Ceiling	Return the nearest integer greater than or equal to argument (same as ceil)
Power	Returns the exponential value of a numeric expression raised to the specified power
Floor	Returns the largest integer less than or equal to the given numeric expression

Amazon Athena

Function Name	Description
Date & Time Functions
Date	Returns the current date as of the start of the query
Time	Returns the current time as of the start of the query
Timestamp	Returns the current timestamp as of the start of the query
Timezone	Returns the current timezone in the format defined by IANA
String Functions
Char	Returns the Unicode code point of the argument 'n' as a single character string
Concat	Returns the string that results from concatenating two string
Length	Returns the length of string 'n' in characters
Left Trim	Removes leading white space from string 'n'
Lower	Converts string to lowercase
Remove	Removes all instances of 'b' from string 'e'
Replace	Replaces all instances of 'b' with 'c' in string 'e'
Reverse	Returns string 'n' with the characters in reverse order
Right Trim	Removes trailing whitespace from string 'n'
Substring	Returns a substring from string 'b' from the starting position 'e'
Substring with Length	Returns a substring from string 'b' of length 'c' from the starting point 'e'
Trim	Removes leading and trailing whitespace from string 'n'
Upper	Converts string 'n' to uppercase
Math Functions
Absolute	Returns the absolute value of the numeric argument 'n'
Cube Root	Returns the cube root of the numeric argument 'n'
Ceiling	Returns the value of 'n' rounded up to the nearest integer
Degrees	Returns the radians value of 'n' to degrees
Exp	Returns the Euler's number raised to the power of 'n'
Floor	Returns the numeric argument 'n' rounded down to the nearest integer
Natural Logarithm	Returns the natural logarithm of 'n'
Base 2 Logarithm	Returns the base 2 logarithm of 'n'
Base 10 Logarithm	Returns the base 10 logarithm of 'n'
Logarithm	Returns the base 'b' logarithm of 'e'
Modulus	Returns the modulus (remainder) of 'b' divided by 'm'
Power	Returns 'b' raised to the power of 'e'
Radians	Returns the converted angle of the argument 'n' in degrees to radians
Random	Returns a pseudo-random number between 0 and the argument 'n'
Round	Returns 'n' rounded to the nearest integer
Round To	Returns 'b' rounded to 'e' decimal places
Sign	Returns the signum function of 'n'
Square Root	Returns the square root of the argument 'n'
Truncate	Returns 'n' rounded to integer by dropping digits after decimal point.
Arc Cosine	Returns the arc cosine of the argument 'n'
Arc Sine	Returns the arc sine of the argument 'n'
Arc Tangent	Returns the arc tangent of the argument 'n'
Arc Tangent 2	Returns the arc tangent of 'b'/'e'
Cosine	Returns the cosine of the argument 'n'
Hyperbolic Cosine	Returns the hyperbolic cosine of the argument 'n'
Sine	Returns the sine of the argument 'n'
Tangent	Returns the tangent of the argument 'n'
Hyper Tangent	Returns the hyperbolic tangent of the argument 'n'
Is Finite	Determines if argument 'n' is finite
Is Infinite	Determines if argument 'n' is infinite
Is Not a Number	Determines if argument 'n' is not-a-number

Amazon RedShift Spectrum

Function Name	Description
Date & Time Functions
Add Months	Adds the specified number of months to a given date expression
Date Add	Returns the datetime
Date Compare	Returns an integer. 0 if identical, -1 if first date is less than second date & 1 if greater than the second date
Date Compare DateTime	Returns an integer. 0 if identical, -1 if date is less than datetime and 1 if first date is greater than datetime
Date Difference	Returns the difference between datetime expressions
Date Part	Return decimal number
Date Truncate	Returns datetime
DateTime Compare Date	Return an integer. 0 if identical, -1 if datetime is less than date and 1 if datetime is greater than date
Extract	Returns the datepart
Interval Compare	Returns an integer. 0 if identical, -1 if first interval is less than second interval and 1 if first interval is greater than second interval
Year	Returns the year of the specified date expression
Math Functions
Absolute	Returns the absolute value of the specified numeric expression
Ceiling	Returns the ceiling value of the specified numeric expression
Exponent	Returns the exponential value of the specified numeric expression
Floor	Returns the ceiling value of the specified numeric expression
Power	Returns the exponential value of the specified numeric expression raised by the second numeric expression
Round	Rounds numbers to the nearest integer or decimal
String Functions
Length	Returns the length of the specified text
Quote Identifier	Returns equivalent quoted identifier
Quote Literal	Returns equivalent quoted literal
Repeat	Repeats text the specified number of times
Replace	Returns all occurrences of the replaced text
Reverse	Returns the reversed text
Right Trim	Returns the trimmed text
String Position	Returns integer representing the position of the second text in the first text expression
Substring	Returns the extracted text from the specified text expressions
Trim	Returns the trimmed text

Google Big Table

Function Name	Description
Date & Time Functions
Datetime difference	Returns the number of whole specified part intervals between two Datetime object
Extract	Returns the value corresponding to the specified date part
Math Functions
Absolute	Returns the absolute value of a double value
Ceiling	Returns the smallest integer that is not less than the value
Exponential	Returns the exponential value of the given float expression
Power	Returns a raised to the power of b
String Functions
Character Length	Returns the length of the STRING in Characters
Left trim	Returns a character expression after removing leading blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Replace	Replaces all occurrences of from value with to value in original value
Reverse string	Returns the reverse of a character expression
Right trim	Returns a character string after truncating all trailing blanks
String concatenation (2 strings)	Concatenates two string value
SubString	Returns a substring of the supplied value
SubString (position and length)	Returns a substring of the supplied value of position and length
Trim	Removes all leading and trailing characters that match value2
Upper case	Returns a character expression after converting lower case character data to upper case

Google Spanner

Function Name	Description
Date & Time Functions
Date Add	Returns a new datetime value based on adding an interval to the specified date
Date Difference	Returns the number of date and time boundaries crossed between two specified dates
Date Part	Returns an integer representing the specified date part of the specified date
Day	Returns an integer representing the day date part of the specified date
Month	Returns an integer representing the month date part of the specified date
Time Difference	Returns the number of time boundaries crossed between two specified dates
To String	Converts a date or datetime value into a string in accordance with the specified format
Year	Returns an integer representing the year date part of the specified date
Mathematical Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Exponential	Returns the exponential value of the evaluated value
Floor	Returns the largest integer less than or equal to the given numeric expression
Power	Returns the base to the exponent power
String Functions
Concat	Concatenates two string values
Left Trim	Remove all characters from left position of a specified character expression
Length	Returns the number of characters
Lower case	Returns a character expression based on the specified starting position and length
Replace	Replaces all found characters with specified source string
Right Trim	Remove all characters from right position of a specified character expression
SubString	Returns a character expression based on the specified string position and length
Upper case	Returns a character expression in which all the characters have been converted to upper case characters

Presto

Function Name	Description
Date & Time Functions
Date difference	Returns the number of date and the time boundaries crossed between two specified dates
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Exponent	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression
String Functions
Left Trim	Returns the string resulting from trimming spaces from left-hand side
Length	Returns the length of the string
Lowercase	Returns the lowercase equivalent of the string
Replace	Returns the replaced equivalent string
Right Trim	Return the string resulting from trimming spaces from right-hand side
Substring	Returns the extracted string
Trim	Returns the string resulting from trimming spaces from both sides
Uppercase	Returns the uppercase equivalent of the string

Amazon Aurora

Function Name	Description
Date & Time Functions
Date add	Returns a new datetime value based on adding an interval to the specified date
Date difference	Returns the number of date and time boundaries crossed between two specified dates
Date part	Returns an integer representing the specified datepart of the specified date
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date
String Functions
Left	Returns the left part of a character string with the specified number of characters
Left trim	Returns a character expression after removing leading blanks
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
Lower case	Returns a character expression after converting upper case character data to lower case
Replace	String replacement
Reverse string	Returns the reverse of a character expression
Right	Returns the right part of a character string with the specified number of characters
Right trim	Returns a character string after truncating all trailing blanks
String concatenation (2 strings)	Concatenates two string values
String concatenation (3 strings)	Concatenates three string values
String concatenation (4 strings)	Concatenates four string values
String concatenation (5 strings)	Concatenates five string values
Substring	Returns part of a character or text
Upper case	Returns a character expression after converting lower case character data to upper case
Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Exponential	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression
Random	Returns a random integer value

Apache Drill/MapR DB

Function Name	Description
Date & Time Functions
Date Add	Returns the added date and time
Date Difference	Returns part of date and time expression
Date Part	Returns part of date and time expression
String Functions
Char Index	Returns the position of the character(s) to be found
Left Trim	Removes leading spaces from a character expression
Length	Returns the length of the character(s)
Lower	Returns the lowercase characters
Right Trim	Removes trailing spaces from a character expression
Substring	Returns a character string
Upper	Returns the uppercase characters
Math Functions
Absolute	Returns the absolute (positive) value
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression
Floor	Returns the largest integer smaller than the given numeric expression
Power	Returns the exponential value of a numeric expression raised to the specified power

Google BigQuery

Function Name	Description
Date & Time Functions
Datetime difference	Returns the number of whole specified part intervals between two Datetime objects
Extract	Returns the value corresponding to the specified date part
Datetime Add	Returns a new datetime value based on adding an interval to the specified date
String Functions
Character Length	Returns the length of the STRING in characters
Left Trim	Returns a character expression after removing leading blanks
Right Trim	Returns a character string after truncating all trailing blanks
Trim	Removes all leading and trailing characters that match value2
Lowercase	Returns a character expression after converting upper case character data to lower case
Uppercase	Returns a character expression after converting lower case character data to upper case
Reverse string	Returns the reverse of a character expression
SubString(position and length)	Returns a substring of the supplied value of position and length
Substring	Returns a substring of the supplied value
Replace	Replaces all occurrences of from_value with to_value in original_value
String concatenation (2 strings)	Concatenates two string values
Math Functions
Absolute	Returns the absolute value of a double value
Exponential	Returns the exponential value of the given float expression
Ceiling	Returns the smallest integer that is not less than the value
Power	Returns e raised to the power of b

Snowflake

Function Name	Description
Date & Time Functions
Date add	Returns a new date value based on adding an interval to the specified date
Date difference	Returns the number of date boundaries crossed between two specified dates
Date part	Returns an integer representing the specified datepart of the specified date
Day	Returns an integer representing the day datepart of the specified date
Month	Returns an integer representing the month datepart of the specified date
Year	Returns an integer representing the year datepart of the specified date

String Functions
Left	Return the leftmost number of characters as specified
Left pads	Returns the left-padded string
Length	Returns the number of characters in the string
Position	Returns the location of a substring in a string
Right	Return the rightmost number of characters as specified
Right pads	Returns a character string of the desired length after padding the existing string to the right
String concatenation (2 strings)	Concatenates two string values
String concatenation (3 strings)	Concatenates three string values
Substring	Returns a character expression based on the specified starting position and length

Math Functions
Absolute	Returns the absolute, positive value of the given numeric expression
Ceiling	Returns the smallest integer greater that, or equal to, the given numeric expression
Exponential	Returns the exponential value of the given float expression
Floor	Returns the largest integer less than or equal to the given numeric expression

Yellowbrick

Function Name	Description
Date & Time Functions
Date Part	Returns the date part of the given date and time expression
Date Truncate	Returns a truncated timestamp to a specified precision
Extract	Returns the extracted timestamp
Year	Returns the Year part of the given date and time expression
Month	Returns the Month part of the given date and time expression
Day	Returns the day part of the given date and time expression
Date Add	Returns the datetime
Date difference	Returns the difference between two timestamps
String Functions
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks
InitCap	Returns a character expression after capitalizing the initial letter
Left	Return the n number of characters specified in the argument from the left of a given string
Right	Return the n number of characters specified in the argument from the right of a given string
Substring	Returns a character expression based on the specified starting position and length
Lowercase	Returns a character expression after converting upper case character data to lower case
Uppercase	Returns a character expression after converting lower case character data to upper case
Left Trim	Remove all characters from left position of a specified character expression
Right Trim	Remove all characters from right position of a specified character expression
Concatenate	Concatenates string expressions
Math Functions
Absolute	Returns the absolute (positive) value
Floor	Returns the largest integer not greater than given argument
Ceiling	Returns the smallest integer not less than the given argument
Power	Returns the exponential value of a numeric expression raised to the specified power
Exponential	Returns e raised to the power of the specified number

Having selected a function, a second function attribute-specific interface is shown (note that this interface is displayed immediately if configuring a function attribute; the information panel shown to the right is only displayed when configuring an existing attribute):

Graphical user interface, text, application, email

Description automatically generated

The interface is used to configure the following specific properties:

• Target Table: you must browse for a Target table using the Choose Database Item dialog. A list of tables within the currently specified database context is shown. Select the required table and click OK.

For more information on using the Choose Database Item dialog, please see the RPI Framework documentation.

• Data Length: only displayed for function attributes that return a string value, this property allows you to define the attribute’s maximum data length. It defaults to 30.

• Arguments list: the specific arguments to be provided are dependent on the function chosen. The function's argument signature is displayed to the right of the interface. For each argument, the following columns are displayed:

o Index: (read-only) a zero-based number that refers to the ordinal position of the argument within the function - note that a summary of the function is displayed to the top right of the interface, and index is used to indicate the position of the argument within the function.

o Value: (updateable): for each argument in a function, you can specify whether its value is to be supplied as an attribute or constant. The default is attribute. You can toggle between Attribute and Constant.

If Constant is selected, you can type a constant value directly into the updateable field.

If Attribute is selected, you can drag and drop an attribute from the toolbox or browse for an attribute using the recent items chooser or File System Dialog. Note that you cannot:

§ Use a model project, exists in table or parameter attribute in this context.

§ Configure a function attribute using itself.

§ Select an attribute with a Target table from a database different to the new function attribute’s Target table’s database.

In addition, if an argument is of a date/time data type, you can also specify that its value be the ‘Current Time’. If used when configuring a function attribute, the attribute cannot be used to build a list comparison criterion.

Note that if a value with an invalid data type is supplied for the argument, a warning message is displayed.

You can also view the File Information Dialog if an attribute is specified.

o Data type: (read-only) one of: string, integer, decimal, date/time.

o Description: (read-only) a textual description of the role played by the argument within the function.