You are here:
Supported Library Functions
In the Data 360 data library, you can choose functions and operators. You can nest functions.
Functions are case-sensitive, and the convention is uppercase letters. For example, enter IF( ), not if( ).
You can reference these functions in the syntax editor.
Conversion Functions
| Function Name | What It Does |
|---|---|
| ABS(number) | Returns the absolute value of a number. Example:
ABS(sourceField['quantity']) with a quantity of -48 returns |
| COMPRESS(value1) | Compresses a string using gzip and returns it in base64. Example:
COMPRESS(sourceField['name']), where name is Andrew Lawrence, returns |
| DECODE_BASE64(value1) | Decodes a string from base64. |
| ENCODE_BASE64(value1) | Encodes a string to base64. |
| FORMATDATE(date, pattern) | Returns the string representation of the given date according to the supplied pattern. Example: FORMATDATE(sourceField['createdDate'],'yyyy-MM-dd') for a date created as Oct 16, 2024 returns 2024-10-16. |
| MD5(stringToHash) | Returns an MD5 hash of the given string. Example: MD5(sourceField['name']),
where name is Andrew Lawrence, returns |
| NUMBER(value1) | Converts a string to a number. |
| PARSEDATE(dateString, pattern) | Returns the date object specified by the given dateString using the given pattern. Example: PARSEDATE(sourceField['createdDate'], 'yyyy-MM-dd'), where createdDate is 2014-02-25, returns the date object 2014-02-25. |
| ROUND(number) | Rounds a number to the nearest integer. Example: ROUND(sourceField['cost']), where cost is 45454.9, returns 45455. If cost is 45454.1, it returns 45454. |
| SHA256(stringToHash) | Returns an SHA256 hash of the given string. Example:
SHA256(sourceField['name']), where name is Andrew Lawrence, returns |
| UNCOMPRESS(value1) | Uncompresses a base64 string using gzip. Example:
UNCOMPRESS(sourceField['name']), where name is |
| UUID() | Returns a 36-character UUID number. |
Date Functions
| Function Name | What It Does |
|---|---|
| DATE(year, month, day) | Returns the date representing the given arguments. |
| DATEDIFF(interval, startDate, endDate) | Returns the number of time units specified by interval between the startDate and endDate. The startDate and endDate arguments must be date objects or date strings in standard form. The interval must be one of the following: yyyy (year), q (quarter), m (month), d (day), ww (week), h (hour), n (minute), s (second). |
| DATEADD(interval, number, date) | Returns the date after which a certain time or date interval is added. The interval must be one of the following: 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), 's' (second). |
| DAYSBETWEEN(startDate, endDate) | Returns the number of days between the given startDate and endDate. The arguments must be date objects or date strings in standard form. |
| DAYS_IN_MONTH(date) | Returns the number of days in a specified month. Example: DAYS_IN_MONTH(sourceField['createdDate']), where createdDate is Oct 16, 2024, returns 31. |
| DAYPRECISION(date) | Returns the given date with day precision, meaning that the hours, minutes, and seconds fields are all set to 0. Example: DAYPRECISION(sourceField['createdDate']), where createdDate is Oct 16, 2024 11:45 AM, returns 2024-10-16 00:00:00. |
| NETWORKDAYS(startDate, endDate) | Returns the number of workdays between two dates, excluding weekends. The arguments must be date objects or date strings in standard form. |
| NOW() | Returns the current system date and time. |
| SETHOUR(date, hour) | Returns the given date set with the given hours. Hour must be a numeric value between 0 and 23. Example: SETHOUR(sourceField['createdDate'], 17), where createdDate is Oct 16, 2024, returns Wed Oct 16 17:00:00 GMT 2024. |
| TODAY() | Returns the current system date in UTC. |
| TOSECONDS(duration) | Returns the number of seconds represented by the given duration string in the format HH:mm:ss/HH:mm:ss.SSS. |
Logical Functions
| Function Name | What It Does |
|---|---|
| IF(condition,resultIfTrue, resultIfFalse) | Returns the second argument if the first argument is true, and third argument if otherwise |
| AND(condition1, condition2) | Logical AND, checks to see that both conditions are true |
| OR(condition1, condition2) | Logical OR, Checks to see if either one of the conditions is true |
| NOT(condition) | Logical NOT, reverses the logic of its argument, true becomes false and vice versa |
Text Functions
| Function Name | What It Does |
|---|---|
| CHAR() | Returns the ASCII character specified by a number. Example: CHAR(61) returns =. |
| CONCAT(text1, text2, ...) | Joins two or more text strings into one string. |
| COALESCE([value1, value2, ...]) | Returns the first value from the list that isn’t empty. |
| COUNT_CHAR(text, charToCount) | Returns the count of a specific character in a text. Example: COUNT_CHAR('Tokyo', 'o') returns 2. |
| EXTRACT(text, delimiter, position) | Extracts from the given text the substring at the given position, after splitting by the given delimiter. Example: EXTRACT('a_b_c', '_', 1) returns b. |
| FIND(findText, withinText) | Returns the starting position of findText in withinText. Returns -1 if withinText doesn’t contain findText. Example: FIND('l', 'Tokyo is a metropolis') returns 18. |
| FUZZYMATCH(valueToMatch, array, threshold) | Searches for a string in an array of values that match using duplicate-detecting algorithms that calculate the similarity of two streams of data. Returns the matched string from the array if there’s a match, and returns an empty string if there’s no match. Example: FUZZYMATCH('ABC Company',['ABC Company Inc','Apple Inc','IBM Company'],0.8) returns ABC Company Inc. The threshold parameter must be a value from 0 through 1. A threshold of 1 means an exact match. |
| ISEMPTY(value) | Indicates whether the value is empty. |
| LEFT(text, numOfChars) | Returns the requested number of characters starting from the left. Example: LEFT(sourceField['name'], 3), where name is Andrew Lawrence, returns And. |
| LEN(text) | Returns the number of characters in the given text. |
| LOWER(text) | Converts all uppercase letters in a text string to lowercase. |
| PROPER() | Capitalizes the first letter of each word in a text string. Example: PROPER('Oh! An octave is a music series of 8 notes.') returns Oh! An Octave Is A Music Series Of 8 Notes. |
| REPLACE(sourceStr, pattern, targetStr) | Replaces every match of pattern (a regex expression) in sourceStr to targetStr. Example: REPLACE('All-The-Best', '-', ' ') returns All The Best. Example: REPLACE('Let It Be',' ','-') returns Let-It-Be. |
| RIGHT(text, numOfChars) | Returns the requested number of characters starting from the right. Example: RIGHT(sourceField['name'], 9), where name is Andrew Lawrence, returns Lawrence. |
| SELECT(sourceStr, pattern) | Selects the first match of the pattern (a regex expression) in sourceStr. Example: SELECT('Hello B wow E Goodbye', 'B(.*?)E') returns B wow E. |
| SPLIT(text, delimiter) | Splits a text string into an array based on the delimiter character. It returns a zero-based, one-dimensional array that includes the parts of the original text string. Example: SPLIT('a,b,c', ',') returns an array with three items. |
| SUBSTRING(text, beginIndex, endIndex) | Returns the substring of the text begining at beginIndex (inclusive) and ending at endIndex (exclusive). |
| SUBSTITUTE(text, oldText, newText) | Substitutes newText for oldText in a text string. Use SUBSTITUTE to replace specific text in a text string. |
| TRIM(text) | Removes all spaces from the text except for single spaces between words. |
| UPPER(text) | Converts all lowercase letters in a text string to uppercase. |
