Query Language Functions

Function

Description

ABS(Number d)

Returns the absolute value of d.

ACOS(Number d)

Returns the arc cosine of an angle.

ASIN(Number d)

Returns the arc sine of an angle.

ATAN(Number d)

Returns the arc tangent of an angle.

ATAN2(Number a, Number b)

Returns the tangent of a/b.

BITAND(Number a, Number b)

Return a & b.

BITNOT(Number a)

Return !a.

BITOR(Number a, Number b)

Returns a | b.

BITXOR(Number a, Number b)

Returns a ^ b.

CEILING(Number d)

Returns the smallest integer that is not less than d.

COS(Number d)

Returns the cosine of an angle.

COT(Number d)

Returns the cotangent of an angle.

DEGREES(Number d)

Converts radians to degrees.

EXP(Number d)

Returns e (2.718...) raised to the power of d.

FLOOR(Number d)

Returns the largest integer that is not greater than d.

LOG(Number d)

Returns the natural logarithm (base e).

LOG10(Number d)

Returns the logarithm (base 10).

MOD(Number a, Number b)

Returns a modulo b.

PI()

Returns pi (3.1415...).

POWER(Number a, Number b)

Returns a raised to the power of b.

RADIANS(Number d)

Converts degrees to radians.

RAND()

Returns a random number x bigger or equal to 0.0 and smaller than 1.0.

ROUND(Number a, Number b)

Rounds a to b digits after the decimal point.

ROUNDMAGIC(Number d)

Solves rounding problems such as 3.11-3.1-0.01.

SIGN(Number d)

Returns -1 if d is smaller than 0, 0 if d equals to 0, and 1 if d is bigger than 0.

SIN(Number d)

Returns the sine of an angle.

SQRT(Number d)

Returns the square root.

TAN(Number a)

Returns the trigonometric tangent of an angle.

TRUNCATE(Number a, Number b)

Truncates a to b digits after the decimal point.

Function

Description

ASCII(String s)

Returns the ASCII code of the leftmost character of s.

BIT_LENGTH(String s)

Returns the length of the string in bits.

CHAR(Integer c)

Returns a character that has the ASCII code c.

CONCAT(String s1, String s2)

Returns s1 + s2.

DIFFERENCE(String s1, String s2)

Returns the difference between the sound of s1 and s2.

HEXTORAW(String s)

Returns translated string.

INSERT(String s, Integer start, Integer length, String replacement)

Returns a string where length number of characters beginning at start has been replaced by replacement.

LENGTH(String s)

Returns the number of characters in s.

LOCATE(String search, String s[, Integer start])

Returns the first index (1=left, 0=not found) where search is found in s, starting at start.

LOWER(String s)

Converts s to lower case.

LPAD(String s1, Integer length[, String s2])

Returns a character string with the length of length characters. The string contains the characters of s1 padded to the left with spaces. If length is smaller than the length of the s1 argument, the argument is truncated. If the optional string s2 is specified, this string is used for padding, instead of spaces.

LTRIM(String s)

Removes all leading blanks in s.

OCTET_LENGTH(String str)

Returns the length of the string in bytes (twice the number of characters).

RAWTOHEX(String s1)

Returns translated string.

REGEXP_MATCHES(String s, String regex)

Returns true if the s matches the regex as a whole. The regex is defined according to regular expression rules.

REGEXP_SUBSTRING(String s, String regex)

Returns the first region in the s that matches the regex. The regex is defined according to regular expression rules.

REPEAT(String s, Integer count)

Returns s repeated count times.

REPLACE(String s, String replace, String s2)

Replaces all occurrences of replace in s with s2.

REVERSE(String s)

Returns a character string based on s with characters in the reverse order.

RPAD(String s1, Integer length[, String s2])

Returns a character string with the length of length characters. The string contains the characters of s1 padded to the right with spaces. If length is smaller than the length of the s1 argument, the argument is truncated. If the optional string s2 is specified, this string is used for padding, instead of spaces.

RTRIM(String s)

Removes all trailing spaces.

SOUNDEX(String s)

Returns a four character code representing the sound of s.

SPACE(Integer count)

Returns a string consisting of count spaces.

SUBSTRING(String s, Integer start[, Integer len])

Returns the substring starting at start (index of first character is 1) with length len.

UPPER(String s)

Converts s to upper case.

Function

Description

CURDATE()

Returns the current date.

CURTIME( [ <time precision> ] )

Returns the current time as a value of TIME WITH TIME ZONE type.

LOCALTIME()

Returns the current time as a value of TIME type.

CURRENT_TIMESTAMP [ ( <timestamp precision> ) ]

Returns the current date/time as a value of TIMESTAMP WITH TIME ZONE type.

LOCALTIMESTAMP [ ( <timestamp precision> ) ]

Returns the current date/time as a value of TIMESTAMP type.

DATEADD(String string, Integer number, Date datetime)

Adds a number of string units to datetime. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

DATEDIFF(String string, Date datetime1, Date datetime2)

Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

DAYNAME(Date date)

Returns the name of the day.

DAYOFMONTH(Date date)

Returns the day of the month (1-31).

DAYOFWEEK(Date date)

Returns the day of the week (1 means Sunday).

DAYOFYEAR(Date date)

Returns the day of the year (1-366).

HOUR(Date time)

Return the hour (0-23).

MINUTE(Date time)

Returns the minute (0-59).

MONTH(Date date)

Returns the month (1-12).

MONTHNAME(Date date)

Returns the name of the month.

NOW()

It's an equivalent of LOCALTIMESTAMP().

QUARTER(Date date)

Returns the quarter (1-4).

SECOND(Date time)

Returns the second (0-59).

SECONDS_SINCE_MIDNIGHT(Date time)

Returns an integer in the range of 0 - 86399 indicating number of seconds elapsed since midnight.

TIMESTAMP(Object value)

This function translates the arguments into a TIMESTAMP WIHOUT TIME ZONE value. When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds. When the argument is a string, it's parsed to a timestamp.

TIMESTAMP_WITH_ZONE(Object value)

This function translates the arguments into a TIMESTAMP value. When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds. When the argument is a string, it's parsed to a timestamp.

TO_CHAR(Date date, String format)

Formats a date into a string of format given in the second argument. The format string is described below.

TO_TIMESTAMP(String value, String format)

Parses datetime to a timestamp using the format. The format string is described below.

UNIX_MILLIS(Date date)

Returns the number of milliseconds since 1970-01-01 (the Epoc).

UNIX_TIMESTAMP(Date date)

Returns the number of seconds since 1970-01-01 (the Epoc).

WEEK(Date date)

Returns the week of this year (1-53).

YEAR(Date date)

Returns the year.

BC | B.C. | AD | A.D.

Returns AD for common era and BC for before common era.

RRRR

4-digit year.

YYYY

4-digit year.

IYYY

4-digit year, corresponding to ISO week of the year. The reported year for the last few days of the calendar year may be the next year.

YY

2 digit year.

IY

2 digit year, corresponding to ISO week of the year .

MM

Month (01-12).

MON

Short three-letter name of month.

MONTH

Name of month.

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year (not a calendar week).

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh (not a calendar week).

IW

Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday. The first week may start near the end of previous year.

DAY

Name of day.

DD

Day of month (01-31).

DDD

Day of year (1-366).

DY

Short three-letter name of day.

HH

Hour of day (00-11).

HH12

Hour of day (00-11).

HH24

Hour of day (00-23).

MI

Minute (00-59).

SS

Second (00-59).

FF

Fractional seconds.

Function

Description

EVALUATE_TO_STRING(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Evaluates an Iotellect expression (specified by expression parameter). The default context for evaluation is specified by defaultContext parameter. The default table is specified by defaultTable parameter. Any field reference pointing to a Data Table field can be used as defaultTable, since in query language Data Table fields of context variables/functions are represented by string fields.

Arguments (argument0, argument1, ...) can be accessed via environment variables named 0, 1, and so on. For example, to access argument3 from the expression use the following reference: {env/3}.

This function converts result of Iotellect expression to a string and returns this string.

EVALUATE_TO_INT(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Works similarly to EVALUATE_TO_STRING, but converts Iotellect expression result to an integer number.

EVALUATE_TO_LONG(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Works similarly to EVALUATE_TO_STRING, but converts Iotellect expression result to a long (bigint) number.

EVALUATE_TO_DOUBLE(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Works similarly to EVALUATE_TO_STRING, but converts Iotellect expression result to a floating point number.

EVALUATE_TO_BOOLEAN(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Works similarly to EVALUATE_TO_STRING, but converts Iotellect expression result to a boolean.

EVALUATE_TO_DATE(String expression, String defaultContext, String defaultTable [, String agrument0, String argument1, ...])

Works similarly to EVALUATE_TO_STRING, but converts Iotellect expression result to a date.

Function

Description

CONVERT(Object exp, String type)

Converts exp to another data type. Supported data types:

• Integer types: SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT, SQL_NUMERIC, SQL_DECIMAL, SQL_BOOLEAN
• Floating point types: SQL_REAL, SQL_FLOAT, SQL_DOUBLE
• String types: SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR
• Date/time types: SQL_DATE, SQL_TIME, SQL_TIMESTAMP

Example: CONVERT(10, SQL_STRING)

CASEWHEN(Object exp, Object v1, Object v2)

If exp is true, v1 is returned, else v2. This function supports nullable fields.

COALESCE(Object exp1, Object exp2, Object exp3, ...)

If exp1 is not null then it is returned else exp2 is evaluated and if not null it is returned and so on.

NULLIF(Object v1, Object v2)

If v1 equals v2 return null, otherwise v1.