Cirro supported functions and additional information.

Stacking Functions

  • If stacking functions, masking functions need to be on the outside otherwise they’ll fail.

For example:

UPPER (MASK(col1))
--fails with error

MASK(UPPER(col1))
--succeeds

Index

The following pages and posts are tagged with

TitleTypeSummary
ABS ABSVAL Page ABS and ABSVAL are both supported, perform the same function, and can be used interchangeably. They returns the absolute value of a number in the same data ...
ACOS Page Returns the arc cosine of a numeric value as a DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
ASIN Page Returns the arc sine of a numeric value as A DOUBLE value. Returns NULL if numValue is NULL. Returns 0 if numValue is 0. Returns an error if numValue is less...
ATAN2 Page Returns the arc tangent of the quotient of two arguments.
ATAN Page Returns the arc tangent of a numeric value as DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
AVG Page AVG returns the aggregate average of values.
BIGINT Page Use of this function converts a value to a BIGINT.
CASE Page The CASE statement is used in place of an expression to return a value based on other values in the query.
CAST Page CAST converts a value from one data type to another.
CEIL Page Returns the smallest whole number that is greater than or equal to the parameter.
CHAR Page CHAR converts a value to CHAR.
CHAR_LENGTH/LENGTH Page CHAR_LENGTH/LENGTH Returns the length of a string value, including spaces and special characters.
CHECKSUM and ROWCHECKSUM Page CHECKSUM returns a N-bit checksum that returns the same result for the same data on different RDBMS'. It has the same syntax and arguments as ROWCHECKSUM.
CLEAR CACHE Page Empties the system cache, or the given cache ID.
COALESCE Page COALESCE returns the first of multiple arguments that is not NULL.
CONVERT Page Use this function to convert a value from one data type to another.
COT Page Returns the cotangent of a numeric value.
COUNT Page COUNT Returns a count of values in the data set.
CREATE REMOTE Page Creates a Cirro function reference which maps to an existing function or functions on remote systems.
CURRENT DATE Page URRENT DATE function returns the current date on the Cirro Data Hub.
CURRENT TIME Page Returns the current time on the Cirro Data Hub.
CURRENT TIMESTAMP Page CURRENT TIMESTAMP function returns a timestamp the current day and time on the Cirro Data Hub.
USER / CURRENT_USER Page Returns the Cirro username used when connecting to Cirro.
CURRENT_UTC_DATE Page CURRENT_UTC_DATE function returns the system date in UTC (Coordinated Universal Time).
CURRENT_UTC_TIME Page CURRENT_UTC_TIME function returns the system time in UTC (Coordinated Universal Time).
CURRENT_UTC_TIMESTAMP Page CURRENT_UTC_TIMESTAMP functions returns the system timestamp in UTC (Coordinated Universal Time).
DATE Page Use this function to convert a non-date value to a DATE type
DATE_TRUNC Page DATE_TRUNC truncates a TIMESTAMP to the specified precision.
DATEADD Page DATEADD adds a specified interval to a date or time value.
DATEDIFF Page DATEDIFF adds a specified interval to a date or time value.
DATENAME Page DATENAME returns the name or numeric value for the specified date component.
DATEPART Page DATEPART returns the numeric portion of a date.
DAY Page DAY returns the day portion of a value.
DAYOFWEEK Page DAYOFWEEK returns the day of the week, as an integer from 1 to 7, where 1 is Sunday.
DAYOFYEAR Page DAYOFYEAR returns the day of the year, as an integer from 1 to 366, where 1 is January 1.
DEGREES Page Performs an inexact conversion of a number from radians to degrees.
DOUBLE Page Converts a value to a DOUBLE type.
DROP_REMOTE_FUNCTION Page Shows the expected data movements and supporting queries for a user-specified query, in the expected execution order.
EXP Page Calculates e (the base of the natural logarithms) raised to the value of the argument.
EXPLAIN Utility Page Shows the expected data movements and supporting queries for a user-specified SELECT query, in the expected execution order.
FLOOR Page Returns the largest whole number that is less than or equal to the parameter.
Accessing Hadoop and Running Cirro Functions through SQL Page Access these functions to query Hadoop data and perform data manipulations.
HASHROW Page Returns the hexadecimal row hash value of one or more expressions.
HOUR Page HOUR returns the hour portion of a value.
INTEGER Page Use this function to convert a value to an INTEGER.
LAST_DAY Page LAST_DAY returns the last day of the month.
LCASE / LOWER Page Converts a string value to lowercase characters.
LEFT Page Returns the leftmost characters in a string value, based on a specified number of characters.
LN / LOG Page Returns the natural logarithm of a number. LN and LOG are both supported, perform the same function, and can be used interchangeably
LOCATE Page If a specified substring is found within a specified search string, LOCATE returns the index at which the substring is found within the search string. If the...
LOG10 Page Returns the base-10 logarithm of a number.
LTRIM Page Trims spaces from the start of a string value.
MASK_CC() Page MASK_CC masks all but the last 4 digits of a standard 16 digit credit card number.
MASK_EMAIL1 () Page The email address is fully masked with the exception of the first and last character.
MASK_EMAIL2 () Page Mask the entire address except the suffix.
MASK_EMAIL3 () Page MASK_EMAIL3 masks the entire email except the domain and suffix.
MASK_FIRST () Page Mask specified number of column characters beginning at left. if no value specified the default is 3.
MASK_FULL() Page Mask an entire data type with a matching mask in any SELECT query.
MASK_HASH () Page Use a hashing function to change the column value. If no type specified, MASK_HASH uses SHA-256.
MASK_LAST () Page Mask specified number of column characters beginning at right.
MASK_NUMERIC () Page MASK_NUMERIC is similar to MASK_HASH except output is masked as numbers. You can specify length and use hashing algorithms like MD5, SHA‌-1, SHA‌-256.
MASK_OUT () Page Mask out masks 60% of characters in any selected column.
MASK_PHONE () Page Mask the last 5 characters of any phone number.
MASK_RANDOM () Page MASK_RANDOM returns a random value from that has the same length as the original value.
MASK_SSN() Page MASK_SSN masks the last 4 characters of the social security number value, and does not accept any other parameter.
MASK_SUB () Page Use MASK_SUB to mask the specified length of characters starting from the startindex.
MAX Page MAX returns the maximum value in a data set.
MIN Page Min returns the minimum value in a data set.
MINUTE Page MINUTE returns the minute portion of a value.
MOD Page Returns the remainder of the division of two integer values.
MONTH Page MONTH returns the month portion of a value.
NATIVE Page NATIVE is a utility function used with SELECT or WHERE clauses. NATIVE executes any command in single quotes exactly as written on the target database.
NULLIF Page NULLIF returns NULL when the two arguments are equivalent.
PI Page Returns the value of PI.
POWER Page Raises one number to the power of a second number.
QUARTER Page QUARTER returns the quart of the year in which the day is found, as an integer from 1 to 4, and where the first quarter of the year is “1”.
RADIANS Page Performs an approximate conversion of a number from degrees to radians.
RAND Page Returns a random number given a seed number.
RANDOM Page Returns a random number.
REPLACE Page Replaces one or more occurrences of a specified string value with another specified value.
RIGHT Page Replaces one or more occurrences of a specified string value with another specified value.
ROWCHECKSUM function Page ROWCHECKSUM returns a N-bit checksum that returns the same result for the same data on different RDBMS'. It has the same syntax and arguments as CHECKSUM.
RTRIM Page Trims spaces from the end of a string value.
SECOND Page SECOND returns the seconds portion of a value.
SIGN Page Returns the sign of a numeric value.
SIN Page Returns the sine of a numeric value.
SMALLINT Page Use this function to convert a value to a SMALLINT.
SQRT Page Returns the square root of a numeric value.
SUBSTR Page Returns a substring of a source string, based on starting position and substring length.
SUM Page SUM returns the aggregate sum of values in a data set..
SYSCS_UTIL.SYSCS_FREE_CACHE() Page Empties the system cache.
SYSTEM_USER Page Returns the username of the user which was used to run the query on the data source.
TAN Page
TIME Page TIME returns the time portion of a value.
TIMESTAMP Page TIMESTAMP returns the timestamp portion of a value.
TIMESTAMPDIFF Date Time Function Page TIMESTAMPDIFF returns the difference between two timestamp values, based on a specified interval type.
TRIM Page Trims spaces from the start and end of a string value.
UCASE / UPPER Page Converts a string value to uppercase characters.
WEEK Page WEEK returns the week of the year in which the day is found, as an integer from 1 to 52, and where the first week of the year is 1.
YEAR Page YEAR returns the year portion of a value.