Transformation Operations Overview#

Datameer provides the following operations to work on and transform your data in alphabetical order:

Operation Name Short Description Category
ABS Returns the absolute value of a numeric expression. Numeric Operations
ACOS Computes the inverse cosine (arc cosine) of its input. Numeric Operations
ADD_MONTHS Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information. Date & Time Operations
ASCII Returns the ASCII code for the first character of a string. String & Binary Operations
ASIN Computes the inverse sine (arc sine) of its argument. Numeric Operations
ATAN Computes the inverse tangent (arc tangent) of its argument. Numeric Operations
BIT_LENGTH Returns the length of a string or binary value in bits. String & Binary Operations
Blend Combines with the contents of a second dataset based on shared content in one or more columns. Datameer Operations
CASE Evaluates a list of conditions until one is TRUE and returns the result. Conditional Expression Operations
CAST Converts a value of one data type into another data type. Conversion Operations
CBRT Returns the cubic root of a numeric expression. Numeric Operations
CEIL Returns values from the expression rounded to the nearest equal or larger integer. Numeric Operations
COALESCE Returns the first non-NULL expression among its arguments. Conditional Expression Operations
CONCAT Concatenates one or more strings, or concatenates one or more binary values. String & Binary Operations
COS Computes the cosine of its argument. Numeric Operations
COT Computes the cotangent of its argument. Numeric Operations
CURRENT_DATE Returns the current date. Context Operations
CURRENT_TIME Returns the current time. Context Operations
CURRENT_TIMESTAMP Returns the current date and time. Context Operations
DATE_FROM_PARTS Creates a date from individual numeric components that represent the year, month, and day of the month. Date & Time Operations
DAY Extracts the day from a date or timestamp. Date & Time Operations
DAYOFWEEK Extracts the day of the week from a date or timestamp. Date & Time Operations
DAYOFMONTH Extracts the day of the month from a date or timestamp. Date & Time Operations
DAYOFYEAR Extracts the day of the year from a date or timestamp. Date & Time Operations
DEGREE Converts radians to degrees. Numeric Operations
DIVO Performs division like the division operator (/). Numeric Operations
ENDSWITH Returns TRUE if the first expression ends with second expression. String & Binary Operations
EXP Computes Euler’s number e (approximately 2.718281) raised to a floating-point value. Numeric Operations
EXTRACT Extracts the specified date or time part from a date, time, or timestamp. Date & Time Operations
Extract Creates a new column from a fixed slice of characters in one of your existing columns. Datameer Operations
Filter Filters the rows in your dataset based on their content. Datameer Operations
FLOOR Returns values from the expression rounded to the nearest equal or smaller integer. Numeric Operations
GREATEST Returns the largest value from a list of expressions. Conditional Expression Operations
HOUR Extracts the hour from a time or timestamp value. Date & Time Operations
IFF If condition evaluates to TRUE, returns expression 1, otherwise returns expression 2. Conditional Expression Operations
IFNULL If expression 1 is NULL, returns expression 2, otherwise returns expression 1. Conditional Expression Operations
INITCAP Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase. String & Binary Operations
LEAST Returns the smallest value from a list of expressions. Conditional Expression Operations
LEFT Returns a leftmost substring of its input. String & Binary Operations
LENGTH Returns the length of an input string or binary value. String & Binary Operations
LN Returns the natural logarithm of a numeric expression. Numeric Operations
LOG Returns the logarithm of a numeric expression. Numeric Operations
LOWER Returns the input string with all characters converted to lowercase. String & Binary Operations
LPAD The argument (base) is left-padded to length length_expr with characters/bytes from the pad argument. String & Binary Operations
LTRIM Removes leading characters, including whitespace, from a string. String & Binary Operations
Manage Columns Controls which columns are included in a dataset. Datameer Operations
MINUTE Extracts the minute from a time or timestamp value. Date & Time Operations
MOD Returns the remainder of input expression 1 divided by input expression 2. Numeric Operations
MONTH Extracts the month from a date or timestamp. Date & Time Operations
MONTHNAME Extracts the three-letter month name from the specified date or timestamp. Date & Time Operations
MONTHS_BETWEEN Returns the number of months between two DATE or TIMESTAMP values. Date & Time Operations
NEXT_DAY Returns the date of the first specified day of week that occurs after the input date. Date & Time Operations
NULLIF Returns NULL if expression is equal to expression 2, otherwise returns expression 1. Conditional Expression Operations
NVL If expression 1 is NULL, returns expression 2, otherwise returns expression 1. Conditional Expression Operations
NVL2 Returns values depending on whether the first input is NULL Conditional Expression Operations
OCTET_LENGTH Returns the length of a string or binary value in bytes. String & Binary Operations
PI Returns the value of pi as a floating-point value. Numeric Operations
POSITION Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position of the first argument in the second argument. String & Binary Operations
POWER Returns a number () raised to the specified power (). Numeric Operations
PREVIOUS_DAY Returns the date of the first specified day of week that occurs before the input date. Date & Time Operations
QUARTER Extracts the quarter from a date or timestamp. Date & Time Operations
RADIANS Converts degrees to radians. Numeric Operations
RANDOM Returns a pseudo-random 64-bit integer. Numeric Operations
RANDSTR Returns a random string of specified length. String & Binary Operations
REGEXP_COUNT Returns the number of times that a pattern occurs in a string. String Operations
REGEXP_INSTR Returns the position of the specified occurrence of the regular expression pattern in the string subject. String Operations
REGEXPR_LIKE Returns TRUE if the subject matches the pattern. String Operations
REGEXPR_REPLACE Returns the subject with the specified pattern either removed or replaced by a replacement string. String Operations
REGEXPR_SUBSTR Returns the substring that matches a regular expression within a string. String Operations
REPEAT Builds a string by repeating the input for the specified number of times. String & Binary Operations
REPLACE Removes all occurrences of a specified substring, and optionally replaces them with another string. String & Binary Operations
Replace Removes all occurrences of a specified substring, and optionally replaces them with another string. Datameer Operations
REVERSE Reverses the order of characters in a string, or of bytes in a binary value. String & Binary Operations
RIGHT Returns a rightmost substring of its input. String & Binary Operations
ROUND Returns rounded values for input_expr. Numeric Operations
RPAD Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value. String & Binary Operations
RTRIM Removes trailing characters, including whitespace, from a string. String & Binary Operations
SIGN Returns the sign of its argument. Numeric Operations
SIN Computes the sine of its argument. Numeric Operations
SECOND Extracts the second from a time or timestamp value. Date & Time Operations
SOUNDEX Returns a string that contains a phonetic representation of the input string. String & Binary Operations
SPLIT Splits a given string with a given separator and returns the result in an array of strings. String & Binary Operations
Split Splits a given string with a given separator and returns the result in an array of strings Datameer Operations
SQRT Returns the square-root of a non-negative numeric expression. Numeric Operations
STRTOK Tokenizes a given string and returns the requested part. String & Binary Operations
SUBSTRING Returns the portion of the string or binary value from , starting from the character/byte specified by , with optionally limited . String & Binary Operations
TAN Computes the tangent of its argument. Numeric Operations
TIME_FROM_PARTS Creates a time from individual numeric components. Date & Time Operations
TO_DATE Converts an input expression to a date. Date & Time Operations
TRANSLATE Translates from the characters in to the characters in . String & Binary Operations
TRIM Removes leading and trailing spaces from a string. String & Binary Operations
TRUNCATE Rounds the input expression down to the nearest (or equal) integer closer to zero, or to the nearest equal or smaller value with the specified number of places after the decimal point. Numeric Operations
UPPER Returns the input string expression with all characters converted to uppercase. String & Binary Operations
WEEK Extracts the week from a date or timestamp. Date & Time Operations
YEAR Extracts the year from a date or timestamp. Date & Time Operations