Built-In Plain Functions

Function

Description

abs(Number col-or-expr)

Returns the absolute value of the numeric value for the column or expression.

cast(Any col-or-expr AS datatype)

Casts the values of the specified column or expression to the specified datatype. See Valid RAQL Datatypes for a list of valid datatypes.

ceiling (Number col-or-expr)

Rounds the numeric value for the column or expression up to the next highest integer value.

char_length (String col-or-expr)

Returns the length of the values in this column or expression as an integer. Returns null if the column value is null.

Note:

in 3.7 and earlier releases, this returned 0 if the column value was null.

character_length (String col-or-expr)

concat(String column-or-literal, String column-or-literal)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

If the value of any column or expression is null, this returns a null value.

custom_month(String col-or-expr)

Extracts the month from dates in the format EEE MMM dd HH:mm:ss z yyyy and returns the number of the month as an integer. This is based on English month names.

"date"(String col-or-expr)

"date"(String col-or-expr, String format)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

date_to_epoc_sec

Represents internally the Java notation of time as milliseconds since midnight, January 1, 1970 UTC.

Conversion of date to seconds (not milliseconds!) since above reference time

Resulting double format compatible with Apama date storage in fractional seconds

Examples

date_to_epoc_sec(1970-01-01‘T’13:33:11.000)

"day"(Date col-or-expr)

"day"(String col-or-expr)

"day"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

dayOfWeek(Date col-or-expr)

dayOfWeek(String col-or-expr)

dayOfWeek(Long col-or-expr)

Converts the values for this column or expression to a date and returns the day of the week as an integer [1 - 7]. This number is specific to the locale for the MashZone NextGen Server. For example, day 1 is Sunday in the United States but Monday in France.

epoc_sec_to_date

Represents internally the Java notation of time as milliseconds since midnight, January 1, 1970 UTC.

Conversion of seconds (not milliseconds!) since above reference time to date

Three variants of function using either string, double, or long as input

Examples

epoc_sec_to_date(‘123456‘)

epoc_sec_to_date(123456.00)

epoc_sec_to_date(123456)

exp(Number col-or-expr)

Raises e, the natural log base, to the power specified by the numeric value or expression passed.

extract_date(Date dt, String field)

Extracts the portion of the date or time from values for this column or expression that is identified by the dt parameter. Valid field values are:

day

day-of-week

hour

minute

month

monthname

quarter

second

week

year

extract_hour(Date dt)

extract_hour(String dt)

extract_hour(Long dt)

Extracts the hour portion of the date and time in the column or expression that is identified by the dt parameter.

extract_minute(Date dt)

extract_minute(String dt)

extract_minute(Long dt)

Extracts the minute portion of the date and time in the column or expression that is identified by the dt parameter.

extract_month(Date dt)

extract_month(String dt)

extract_month(Long dt)

Extracts the month portion of the date and time in the column or expression that is identified by the dt parameter.

extract_second(Date dt)

extract_second(String dt)

extract_second(Long dt)

Extracts the second portion of the date and time in the column or expression that is identified by the dt parameter.

extract_week(Date dt)

extract_week(String dt)

extract_week(Long dt)

Extracts the week portion of the date and time in the column or expression that is identified by the dt parameter.

extract_year(Date dt)

extract_year(String dt)

extract_year(Long dt)

Extracts the year portion of the date and time in the column or expression that is identified by the dt parameter.

floor (Number column)

Rounds the numeric value or expression down to the next closest integer value.

format_date(dateColumn, datePattern, TimeZone)

Represents string of date values in target format and target timezone.

datePattern may be 'short', 'medium', 'long', 'full' or any date pattern as specified in Java's SimpleDateFormat.

TimeZone may be any time zone as specified in Java's SimpleDateFormat

Examples

format_date ( dateColumn , ' yyyy -MM- dd ', 'GMT')

format_date ( dateColumn , ' h:mm a ', 'PST-08:00')

format_date (dateColumn, datePattern)

Corresponds to format_date(dateColum, datePattern, 'GMT')

format_date (dateColumn)

Corresponds to format_date(dateColum, 'yyyy-MM-dd'T'HH:mm:ss.SSSZ', 'GMT')

geo_distance(String column-or-lat1, String column-or-long1, String column-or-lat2, String column-or-long2)

Calculates the geographical distance between two locations identified by latitude and longitude. Commonly, one set of coordinates is provided from two columns or expressions in the data and the other is provided as literal values.

If any parameter value is null, this returns a null value.

"hour"(Date col-or-expr)

"hour"(String col-or-expr)

"hour"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

lower(String col-or-expr)

Converts the values for this column or expression to all lower-case characters.

ln(Number col-or-expr)

Calculates the natural log of the numeric value or expression. Column values or expressions must be greater than zero.

matches(String col-or-expr, String pattern)

Determines if a string matching the regular expression in pattern exists anywhere within the values of the column or expression. Matching is case insensitive. Returns a boolean value (true or false).

For example:

where matches(firstname,'ra[n|l]')='true'

Will match Frank, Gerald or Randy in the firstname column. To perform exact regular expression matches, with no implicit wildcards, see the regex function.

"minute"(Date col-or-expr)

"minute"(String col-or-expr)

"minute"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

mod(Number dividend-col-or-expr, Number divisor-col-or-expr)

Returns the remainder of dividing the numeric value or expression of the dividend by the numeric value or expression of the divisor.

"month"(Date col-or-expr)

"month"(String col-or-expr)

"month"(Long col-or-expr)

"month"(Object col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

nvl(String col-or-expr, String str)

nvl(Date col-or-expr, String str)

nvl(Number col-or-expr, String str)

Replaces null values in the specified column or expression with the specified string value.

Note:

In previous releases, string columns with only white space characters (space, tab, etc.) were treated as a null value.

Effective in 3.8, columns with string values are considered to be null only if they have no value or they contain an empty string.

power(Number base-col-or-expr, Number exponent-col-or-expr)

Calculates the value of the base number or expression raised to the power of the exponent value or expression.

quarter(Date col-or-expr)

quarter(String col-or-expr)

quarter(Long col-or-expr)

Converts the values for this column or expression to a date and returns the number of the quarter as an integer.

regex(String col-or-expr, String pattern)

Determines if a string matching the regular expression in pattern exists in the values of the column or expression as specified. Matching is case insensitive. Returns a boolean value (true or false).

Note:

This function does not add any implicit wildcards to the regular expression. To find a string anywhere within column values, use the matches function.

For example:

where regex(firstname,'ra[n|l]')='true'

Will match Ralph or Randy in the firstname column, but will not match Frank or Gerald.

round(Number val)

round(Number val, Integer decimal-places)

Rounds the numeric value or expression to 0 decimal places, if no decimal places are specified, or to the number of decimal places passed as an argument. If any value is null, this returns a null value.

Note:

This function has changed in 3.8 in two respects:

In 3.7 and earlier releases, this function rounded the results to 2 decimal places if no decimal places were specified.

Column or expression values must be numeric. This function no longer implicitly casts values.

"second"(Date col-or-expr)

"second"(String col-or-expr)

"second"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

split_part(String col-or-expr, String delim, Integer part)

split_part(String col-or-expr, Character delim, Integer part)

Returns the nth part of the values of this column or expression when each value is split into parts at each delimiter defined in delim. For example:

split_part("INV:2012:GHI345",":",3)

Returns the string "GHI345".

split_regex((String col-or-expr, String pattern, Integer part)

This function is identical to split_part with the exception that the delimiter used to split the column values from pattern is a regular expression.

sqrt(Numer col-or-expr)

Calculates the square root of the specified numeric value or expression.

"string"(String col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

substr(String col-or-expr, Integer begin, Integer end)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

substring(String col-or-expr FROM Integer start-position [FOR Integer length])

Extracts the number of characters specified as length from the value or expression starting from the start-position. If length is not specified, extracts all characters from the start-position to the end of the string.

Character positions are one-based (the first character is 1).

time_mask(Date column, String mask)

time_mask(String column, String mask)

Converts the value for this column or expression to a date and time, if needed, in the form yyyy-MM-dd:HH:mm:ss.

Note:

If the values for the column do not contain times, the time is set to 00:00:00.

It extracts the portion of this date and time through the time part identified by the mask.

Valid masks include:

y = returns only the year.

M = returns the year and month.

d = returns the year, month and day.

H = returns the full date and hour.

m = returns the full date, hour and minute.

s = returns the full date and time.

to_date(String col-or-expr)

to_date(String col-or-expr, String format)

Casts the values for this column or expression to Date.

If no format pattern is provided, this function can have a negative impact on performance as it attempts to convert the string iterating over the following date formats:

M/dd/yy or the short format for the current locale

MMM dd, yyyy or the long format for the current locale

EEE MMM dd, yyyy or the full format for the current locale

yyyy-MM-dd

EEE MM dd HH:mm:ss z yyyy

EEE, dd MMM yyyy HH:mm:ss Z

yyyy-MM-DD'T'HH:mm:ss'Z'

Use the format parameter to define the date and time pattern used in values for this column.

Note:

For more information on date format patterns, see the Java SimpleDateFormat class.

to_long(String col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

trim( [LEADING | TRAILING | BOTH] [String character] [FROM] String target-col-or-expr)

Removes the specified character from the string values of the target column or expression in the specified locations:

LEADING = removes any number of the specified character at the beginning of the target string.

TRAILING

BOTH = removes any number of both leading and trailing characters from the target string. If no location is specified, this is the default.

If the character to remove is not specified, this defaults to removing space characters.

truncate(Number col-or-expr, Number decimals)

For numeric values or expressions, this truncates the number to the number of decimals places specified. If the decimals parameter is omitted, truncates the number to zero decimals.

upper(String col-or-expr)

Converts the values for this column or expression to all upper-case characters.

"week"(Date col-or-expr)

"week"(String col-or-expr)

"week"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.

week_of_month(Date col-or-expr)

week_of_month(String col-or-expr)

week_of_month(Long col-or-expr)

Converts the values for this column or expression to a date and returns the number of the week for this month as an integer.

"year"(Date col-or-expr)

"year"(String col-or-expr)

"year"(Long col-or-expr)

Deprecated. See Update Built-In Plain Functions for 3.8 for alternatives.