Prepare function tooltips
ABS
Syntax |
Description |
Sample usage |
---|---|---|
(LONG|DOUBLE|DECIMAL) |
Returns the absolute value of an expression. Note: If the result is not negative (x ≥ 0), the result is returned. If the result is negative (x < 0), the negation of the result is returned. |
(-7) will return 7
|
ACOS
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE)
|
Returns the arc cosine of an expression.
|
(.13) will return 1.440427347091751 |
ASIN
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE)
|
Returns the arc sine of an expression.
|
(.442) will return 0.45782706881105106 |
ATAN
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE)
|
Returns the arc tangent of an expression.
|
(1) will return 0.7853981633974483 |
CBRT
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE)
|
Returns the cube root of an expression.
|
(-8) will return -2 |
CEIL
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE)
|
Returns the value of an expression rounded up to the nearest integer. Note: This function never decreases the result value. |
(-3.23) will return -3 |
Input/output (CEIL)
x |
CEIL (x) |
---|---|
4.6 |
5 |
3.5 |
4 |
2.4 |
3 |
-1.0 |
-1 |
-2.4 |
-2 |
-3.5 |
-3 |
CONCAT
Syntax |
Description |
Sample usage |
---|---|---|
(EXPRESSION, EXPRESSION)
|
Concatenates (chains together) two expressions of identical type. Note: If either expression is null, the resulting expression is null. |
('80', '7') will return 807
|
ConvertDdMonYy
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts string representing a date to ISO format. |
('25-DEC-17') will return 2018-12-25 |
ConvertMmDdYyyy
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts string representing a date to ISO format. |
('12/25/2018') will return 2018-12-25 |
ConvertYYYYMMDD
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts string representing a date to ISO format. |
('2018/12/25') will return 2018-12-25 |
COS
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the cosine of an expression. |
(.55) will return 0.8525245220595057 |
COSH
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the hyperbolic cosine of an expression. |
(4) will return 27.308232836016487 |
CurrentDateTimeFormatted
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a string representing the date of when the Pig job started in a format based on the string argument (defined by the user). |
('DD/MM/YYYY') will return 12/25/2018 |
CurrentDateTimeString
Syntax |
Description |
Sample usage |
---|---|---|
No arguments required, use () |
Returns a string representing the date of when the Pig job started in a standard ISO format (e.g., 2015-12-14T17:49:11.686-05:00, representing year, month, day, hours, minutes, seconds, milliseconds, and timezone in that order.) |
returns 2018-12-25T17:49:11.686-05:00 |
DateAddDays
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, INTEGER, BOOLEAN) |
Returns string representing a date after adding specified number of days (could be any +positive or -negative integer) to the given date. Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null |
('2018-12-20', 2, true) will return 2018-12-22 or (REMIT_PYMTDATE, 2, true) will return 2018-12-22 where REMIT_PYMTDATE=2018-12-20 for that record. |
DateCompare
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, BOOLEAN) |
Returns integer indicating whether the second data is equal, earlier, or later than the first date. 0 = equal 1 = stringDate1 is after stringDate2 -1 = stringDate1 is before stringDate2 Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null |
('2018-12-21', '2018-12-20') will return 1 ('2018-12-20', '2018-12-20') will return 0 ('2018-12-20', '2018-12-21') will return -1 |
DateConvertDdMonYy
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts a string representing a date to ISO format. |
('25-DEC-2018') will return 2018-12-25 |
DateConvertMmDdYyyy
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts a string representing a date to ISO format. |
('12/25/2018') will return 2018-12-25 |
DateConvertYYYYMMDD
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts a string representing a date to ISO format. |
('2018/12/25') will return 2018-12-25 |
DateCurrent
Syntax |
Description |
Sample usage |
---|---|---|
No arguments required, use () |
Returns a string representing the current date. |
returns 2018-02-11 |
DateDaysDifference
Syntax |
Description |
Sample usage |
---|---|---|
(startDate, endDate) |
Returns the number of days between two dates. |
('2018-12-20', '2018-12-25') returns 5 ('2018-12-25', '2018-12-20') returns -5 ('2018-01-01', '2018-01-01') returns 0 |
DateFirstDayOfMonth
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns the first day of the month for the input string representing a date.
|
('2018-12-25') will return 2018-12-01 |
DateGetWeekdayNumber
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns ISO weekDayNumber (Monday = 1, Tuesday=2, and so on..) for string input representing a date. |
('2018-12-25') will return 5 (Friday) |
DateIsBetweenExclusive
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, STRING, BOOLEAN) |
Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, excluding both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null
|
('2018-12-21', '2018-12-20', '2018-12-25') will return true ('2018-12-20', '2018-12-20', '2018-12-25') will return false |
DateIsBetweenInclusive
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, STRING, BOOLEAN) |
Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, including both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null |
('2018-12-21', '2018-12-20', '2018-12-25') will return true |
DateIsEqual
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, BOOLEAN) |
Returns boolean stating two input dates are equal or not. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null
|
('2018-12-21', '2018-12-20') will return false ('2018-12-20', '2018-12-20') will return true |
DateIsValidFormat
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, BOOLEAN) |
Returns boolean if the specified date input matches the input format, otherwise false. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null |
('2018-12-22', 'yyyy-MM-dd') will return true ('2018-12-22', 'yyyy/MM/dd') will return false |
DateParseFormat
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, BOOLEAN) |
Returns ISO date for the specified input date string. Input format string param specified by user (provide the format applied to input string representing a date). Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null |
('2018/12/22', 'yyyy/MM/dd') will return 2018-12-22 ('20181222', 'yyyyMMdd') will return 2018-12-22 |
DateRollBackwardToWeekDayByWeek
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, INTEGER, INTEGER, BOOLEAN) |
Returns the date based on requested day (backward) of the week of given date. dayNum = > Monday = 1, Tues = 2 and so on...till SunDay = 7. intWeekNum=> lastweek =0, one week before last week = 1, two weeks before last week=2, and so on. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null
|
('2019-01-11', 3, 1) will return 2019-01-02 (the Wednesday 10 days before) |
DateRollForwardToWeekDay
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, INTEGER, BOOLEAN) |
Returns the date based on requested day (forward) of the week of given date.dayNum = > Monday = 1, Tues = 2 and so on...till SunDay = 7. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null |
('2019-01-11', 1, false) will return 2019-01-14 |
DiceCoefficient
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Returns the number of tokens (character pairs) common between two strings divided by the total number of tokens. |
('frog', 'fog') returns 0.4 |
DoubleAbs
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns a single numeric value, absolute value of the argument. |
(-8.4567890) will return 8.456789 |
ENDSWITH
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Determines if the first argument ends with the string in the second.
|
(Major, 'y') will return true for fields ending with 'y' (Psychology, 'y') and false for fields that do not end with 'y' (Accounting, 'y') |
EqualsIgnoreCase
Syntax |
Description |
Sample usage |
---|---|---|
BOOLEAN (STRING, STRING) |
Determines if two strings are equal (ignoring case). |
('field_a', 'Field_b') will return false ('field_a', 'Field_a') will return true |
EXP
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns Euler's number e raised to the power of x. |
(.5) will return 1.6487212707001282 |
FLOOR
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the value of an expression rounded down to the nearest integer. Note: This function never increases the result value. |
(-3.23) will return -4 (3.23) will return 3 |
Input/output (FLOOR)
x |
(x) |
---|---|
4.6 |
4 |
3.5 |
3 |
2.4 |
2 |
1.0 |
1 |
-1.0 |
-1 |
-2.4 |
-3 |
-3.5 |
-4 |
INDEXOF
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Returns the index of the first occurrence of a character in a string, searching forward from a start index. |
('banana' , 'b') will return 0 ('banana' , 'a') will return 1 ('banana' , 'n') will return 2 |
IS_DOUBLE
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns boolean whether or not the input string is a Double. |
('5500.00') will return true ('5500') will return true ('JOB_TITLE') will return false |
IS_INTEGER
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns boolean whether or not the input string is an Integer.
|
('Billy') will return false ('9') will return true ('9.1') will return false |
IS_LONG
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns boolean whether or not the input string is type long. |
(89745463.9876) will return false (if not stored as a long data type) |
IS_NULL
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|LONG|DOUBLE|DECIMAL) |
Returns boolean whether or not the input string is NULL. |
Null fields will return true |
IS_NULL_OR_EMPTY
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns boolean whether or not the input string is NULL or EMPTY. |
Null or empty fields will return true |
LAST_INDEX_OF
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Returns the index of the last occurrence of a character in a string, searching backward from the end of the string. The index begins at 0. |
('madam', 'm') will return 4 |
LCFIRST
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts the first character in a string to lower case. |
('Marlboro') will return marlboro |
LevenshteinDistance
Syntax |
Description |
Sample usage |
---|---|---|
LevenshteinDistance |
Returns the number of changes needed to change one sequence into another, where each change is a single character modification (deletion, insertion, or substitution) |
('frog', 'fog') returns 1 |
LongestCommonSubsequence
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Returns the length of the longest subsequence that two strings have in common. Two strings that are entirely different, return a value of 0, and two strings that return a value of the commonly shared length implies that the strings are completely the same in value and position. |
('axbyczqrs', 'abcxyzqtv') returns 4 |
LOG
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the natural logarithm (base e) of an expression.
|
(4) will return 1.3862943611198906 |
LOG10
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the base 10 logarithm of an expression. |
(4) will return 0.6020599913279624 |
LOWER
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Converts all characters in a string to lower case. |
('MARLBORO') will return marlboro |
LTRIM
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a copy of the string with only leading white space removed. |
(' Marlboro') will return Marlboro (spaces before the string have been removed) |
NextSequence
Syntax |
Description |
Sample usage |
---|---|---|
(""schemaName"", "sequenceName") |
Returns next value for the desired sequence as per the increment by and last value obtained by select * from podium_core.pd_role_nid_seq; from postgres database. |
(""jdbc:postgresql://localhost:5432/podium_md?user=postgres&password=password"", ""pd_role_nid_seq"") will return nextval for seq pd_role_nid_seq |
NVL
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Returns second string when a null value for first string is encountered. |
(Major, 'n/a') will return 'n/a' if the 'Major' field contains a null value. Otherwise, it would return the field value. |
OBFUSCATE
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|LONG|DOUBLE|BOOLEAN|DECIMAL, STRING) |
Obfuscates a string or number to string value using the obfuscation rule. Obfuscation rules must already be defined in the metadata with the exact rule name. See Obfuscation methods for information on available rules . |
('Input') will return ('ObfuscatedInput') dependent on obfuscation rule applied. |
RANDOM
Syntax |
Description |
Sample usage |
---|---|---|
no arguments required, use () |
Returns a pseudo random number (DOUBLE) greater than or equal to 0.0 and less than 1.0. |
() will return a different pseudo-randomly generated value (greater than or equal to 0.0 and less than 1) for each record in that field(column). These values take the place of the values in that output column for the field in which this function is entered. example of output values: 0.8448849155781958 0.8179221440864705 0.7165316814860472 |
REGEX_EXTRACT
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, INTEGER) |
Matches regular expression and extracts the matched group defined by index parameter.
|
(Client, 'e', 0) returns e in those fields with an e present (the first index) ('Client', 'e', 0) returns e (the e in the string 'client') |
REPLACE
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING, STRING) |
Replaces existing characters in a string with new characters.
|
('marlboro', 'o', 'a') returns marlbara
|
ROUND
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the value of an expression rounded to an integer.
|
(1.02) will return 1 (-3.2) will return 3 |
Input/Output (ROUND)
x |
ROUND (x) |
---|---|
4.6 |
5 |
3.5 |
4 |
2.4 |
2 |
1.0 |
1 |
-1.0 |
-1 |
-2.4 |
-2 |
-3.5 |
-3 |
-4.6 |
-5 |
ROUND_TO
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE|DECIMAL, INTEGER) |
Returns the value of an expression rounded to a specified number of decimal digits. |
(1234.56, -2) will return 1200.0 |
RTRIM
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a copy of a string with only trailing whitespace removed. |
('Marlboro ') will return Marlboro (spaces after the string have been removed) |
SIN
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the sine of an expression. |
(.75) will return 0.6816387600233341 |
SINH
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the hyperbolic sine of an expression. |
(.75) will return 0.82231673193583 |
SIZE
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|INTEGER|LONG|DOUBLE) |
Computes the number of elements based on any data type. SIZE includes NULL values in the size computation. SIZE is not algebraic. If the tested object is null, the SIZE function returns null. |
(Joe) will return 3 (Petunia) will return 7 (00036) will return 1 (1.2679897) will return 1 |
SPRINTF
Syntax |
Description |
Sample usage |
---|---|---|
(FORMAT, STRING, STRING) |
Formats string inputs into a new string based on a printf-style template.
|
('App Version %s %s-%s', 'qdc', TO_STRING(4.2), 'patch') will return "App Version qdc 4.2-patch" |
SQRT
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the positive square root of an expression. |
(9) will return 3.0 |
STARTSWITH
Syntax |
Description |
Sample usage |
---|---|---|
(STRING, STRING) |
Determines if the first argument starts with the second string. |
('llama', 'm') will return false ('marlboro', 'm') will return true |
SUBSTRING
Syntax |
Description |
Sample usage |
---|---|---|
(SUBSTRING, STARTINDEX, LENGTH) |
Returns a subset of the given string. |
StartIndex is zero based. If passed in string is null, UDF returns null. If startIndex<0 OR startIndex>[stringlength] return null If length<0 returns null If length==0 returns empty string if length>[string length] return till the last character
("abc", 2,1) returns c ("abc",2,0) returns "" ("abc",2,-1) returns NULL ("abc", 2, 5) returns c (NULL,0,1) returns NULL ("",0,1) returns NULL |
TAN
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the tangent of an expression. |
(4) will return 1.1578212823495777
|
TANH
Syntax |
Description |
Sample usage |
---|---|---|
(DOUBLE) |
Returns the hyperbolic tangent of an expression. |
(4) will return 0.999329299739067
|
TO_DECIMAL
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|LONG|DOUBLE) |
Casts input to type decimal. |
(89745463.9876) will return 89745463.9876 |
TO_DOUBLE
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|LONG|DECIMAL) |
Casts input to type double. |
('15678') will return 15678.0 |
TO_INTEGER
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|DOUBLE|DECIMAL) |
Casts input to type integer. |
(1.9) will return 1 |
TO_LONG
Syntax |
Description |
Sample usage |
---|---|---|
(STRING|DOUBLE|DECIMAL) |
Casts input to type long. |
(89745463.9876) will return 89745463 |
TO_STRING
Syntax |
Description |
Sample usage |
---|---|---|
(LONG|DOUBLE|BOOLEAN|DECIMAL) |
Casts input to type string. |
(223) will return 223 |
TRIM
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a copy of a string with leading and trailing white space removed. |
(' Marlboro ') will return Marlboro (spaces before and after string have been removed) |
UCFIRST
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a string with the first character converted to upper case. |
('marlboro') will return Marlboro |
UniqueID
Syntax |
Description |
Sample usage |
---|---|---|
no arguments required, use () |
Returns strings representing field's unique ID for each record. The ID takes form "taskindex-sequence". |
() will return ID values ('0-80', '0-71', '0-106')
|
UPPER
Syntax |
Description |
Sample usage |
---|---|---|
(STRING) |
Returns a string converted to upper case. |
('marlboro') will return MARLBORO |