+ - * /

Standard numeric operators

x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x ^[a]

ABS(x)

Absolute value of x

See standard numeric operators above

ACOS(x)

Arc cosine of x

x in {double, bigdecimal}, return type is double

ASIN(x)

Arc sine of x

x in {double, bigdecimal}, return type is double

ATAN(x)

Arc tangent of x

x in {double, bigdecimal}, return type is double

ATAN2(x,y)

Arc tangent of x and y

x, y in {double, bigdecimal}, return type is double

CEILING(x)

Ceiling of x

x in {double, float}, return type is double

COS(x)

Cosine of x

x in {double, bigdecimal}, return type is double

COT(x)

Cotangent of x

x in {double, bigdecimal}, return type is double

DEGREES(x)

Convert x degrees to radians

x in {double, bigdecimal}, return type is double

EXP(x)

e^x

x in {double, float}, return type is double

FLOOR(x)

Floor of x

x in {double, float}, return type is double

FORMATBIGDECIMAL(x, y)

Formats x using format y

x is bigdecimal, y is string, returns string

FORMATBIGINTEGER(x, y)

Formats x using format y

x is biginteger, y is string, returns string

FORMATDOUBLE(x, y)

Formats x using format y

x is double, y is string, returns string

FORMATFLOAT(x, y)

Formats x using format y

x is float, y is string, returns string

FORMATINTEGER(x, y)

Formats x using format y

x is integer, y is string, returns string

FORMATLONG(x, y)

Formats x using format y

x is long, y is string, returns string

LOG(x)

Natural log of x (base e)

x in {double, float}, return type is double

LOG10(x)

Log of x (base 10)

x in {double, float}, return type is double

MOD(x, y)

Modulus (remainder of x / y)

x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x

PARSEBIGDECIMAL(x, y)

Parses x using format y

x, y are strings, returns bigdecimal

PARSEBIGINTEGER(x, y)

Parses x using format y

x, y are strings, returns biginteger

PARSEDOUBLE(x, y)

Parses x using format y

x, y are strings, returns double

PARSEFLOAT(x, y)

Parses x using format y

x, y are strings, returns float

PARSEINTEGER(x, y)

Parses x using format y

x, y are strings, returns integer

PARSELONG(x, y)

Parses x using format y

x, y are strings, returns long

PI()

Value of Pi

return is double

POWER(x,y)

x to the y power

x in {double, bigdecimal, biginteger}, return is the same type as x

RADIANS(x)

Convert x radians to degrees

x in {double, bigdecimal}, return type is double

RAND()

Returns a random number, using generator established so far in the query or initializing with system clock if necessary.

Returns double.

RAND(x)

Returns a random number, using new generator seeded with x.

x is integer, returns double.

ROUND(x,y)

Round x to y places; negative values of y indicate places to the left of the decimal point

x in {integer, float, double, bigdecimal} y is integer, return is same type as x

SIGN(x)

1 if x > 0, 0 if x = 0, -1 if x < 0

x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer

SIN(x)

Sine value of x

x in {double, bigdecimal}, return type is double

SQRT(x)

Square root of x

x in {long, double, bigdecimal}, return type is double

TAN(x)

Tangent of x

x in {double, bigdecimal}, return type is double

BITAND(x, y)

Bitwise AND of x and y

x, y in {integer}, return type is integer

BITOR(x, y)

Bitwise OR of x and y

x, y in {integer}, return type is integer

BITXOR(x, y)

Bitwise XOR of x and y

x, y in {integer}, return type is integer

BITNOT(x)

Bitwise NOT of x

x in {integer}, return type is integer

x || y

Concatenation operator

x,y in {string}, return type is string

ASCII(x)

Provide ASCII value of the left most character in x. The empty string will as input will return null. ^[a]

return type is integer

CHR(x) CHAR(x)

Provide the character for ASCII value x ^[a]

x in {integer}

CONCAT(x, y)

Concatenates x and y with ANSI semantics. If x and/or y is null, returns null.

x, y in {string}

CONCAT2(x, y)

Concatenates x and y with non-ANSI null semantics. If x and y is null, returns null. If only x or y is null, returns the other value.

x, y in {string}

INITCAP(x)

Make first letter of each word in string x capital and all others lowercase

x in {string}

INSERT(str1, start, length, str2)

Insert string2 into string1

str1 in {string}, start in {integer}, length in {integer}, str2 in {string}

LCASE(x)

Lowercase of x

x in {string}

LEFT(x, y)

Get left y characters of x

x in {string}, y in {integer}, return string

LENGTH(x)

Length of x

return type is integer

LOCATE(x, y)

Find position of x in y starting at beginning of y

x in {string}, y in {string}, return integer

LOCATE(x, y, z)

Find position of x in y starting at z

x in {string}, y in {string}, z in {integer}, return integer

LPAD(x, y)

Pad input string x with spaces on the left to the length of y

x in {string}, y in {integer}, return string

LPAD(x, y, z)

Pad input string x on the left to the length of y using character z

x in {string}, y in {string}, z in {character}, return string

LTRIM(x)

Left trim x of blank chars

x in {string}, return string

QUERYSTRING(path [, expr [AS name] ...])

Returns a properly encoded query string appended to the given path. Null valued expressions are omitted, and a null path is treated as ''.

Names are optional for column reference expressions.

e.g. QUERYSTRING('path', 'value' as "&x", ' & ' as y, null as z) returns 'path?%26x=value&y=%20%26%20'

path, expr in {string}. name is an identifier

REPEAT(str1,instances)

Repeat string1 a specified number of times

str1 in {string}, instances in {integer} return string

REPLACE(x, y, z)

Replace all y in x with z

x,y,z in {string}, return string

RIGHT(x, y)

Get right y characters of x

x in {string}, y in {integer}, return string

RPAD(input string x, pad length y)

Pad input string x with spaces on the right to the length of y

x in {string}, y in {integer}, return string

RPAD(x, y, z)

Pad input string x on the right to the length of y using character z

x in {string}, y in {string}, z in {character}, return string

RTRIM(x)

Right trim x of blank chars

x is string, return string

SUBSTRING(x, y)

Get substring from x, from position y to the end of x

y in {integer}

SUBSTRING(x, y, z)

Get substring from x from position y with length z

y, z in {integer}

TO_CHARS(x, encoding)

Return a clob from the blob with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.^[b]

x is a blob, encoding is a string, and returns a clob

TO_BYTES(x, encoding)

Return a blob from the clob with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.^[b]

x in a clob, encoding is a string, and returns a blob

TRANSLATE(x, y, z)

Translate string x by replacing each character in y with the character in z at the same position

x in {string}

UCASE(x)

Uppercase of x

x in {string}

UNESCAPE(x)

Unescaped version of x. Possible escape sequences are \b - backspace, \t - tab, \n - line feed, \f - form feed, \r - carriage return. \uXXXX, where X is a hex value, can be used to specify any unicode character. \XXX, where X is an octal digit, can be used to specify an octal byte value. If any other character appears after an escape character, that character will appear in the output and the escape character will be ignored.

x in {string}

CURDATE()

Return current date

returns date

CURTIME()

Return current time

returns time

NOW()

Return current timestamp (date and time)

returns timestamp

DAYNAME(x)

Return name of day

x in {date, timestamp}, returns string

DAYOFMONTH(x)

Return day of month

x in {date, timestamp}, returns integer

DAYOFWEEK(x)

Return day of week (Sunday=1)

x in {date, timestamp}, returns integer

DAYOFYEAR(x)

Return Julian day number

x in {date, timestamp}, returns integer

FORMATDATE(x, y)

Format date x using format y

x is date, y is string, returns string

FORMATTIME(x, y)

Format time x using format y

x is time, y is string, returns string

FORMATTIMESTAMP(x, y)

Format timestamp x using format y

x is timestamp, y is string, returns string

FROM_UNIXTIME (unix_timestamp)

Return the Unix timestamp (in seconds) as a Timestamp value

Unix timestamp (in seconds)

HOUR(x)

Return hour (in military 24-hour format)

x in {time, timestamp}, returns integer

MINUTE(x)

Return minute

x in {time, timestamp}, returns integer

MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone)

Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones.  i.e. if the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6.  The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8.

startTimeZone and endTimeZone are strings, returns a timestamp

MODIFYTIMEZONE (timestamp, endTimeZone)

Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process.

Timestamp is a timestamp; endTimeZone is a string, returns a timestamp

MONTH(x)

Return month

x in {date, timestamp}, returns integer

MONTHNAME(x)

Return name of month

x in {date, timestamp}, returns string

PARSEDATE(x, y)

Parse date from x using format y

x, y in {string}, returns date

PARSETIME(x, y)

Parse time from x using format y

x, y in {string}, returns time

PARSETIMESTAMP(x,y)

Parse timestamp from x using format y

x, y in {string}, returns timestamp

QUARTER(x)

Return quarter

x in {date, timestamp}, returns integer

SECOND(x)

Return seconds

x in {time, timestamp}, returns integer

TIMESTAMPCREATE(date, time)

Create a timestamp from a date and time

date in {date}, time in {time}, returns timestamp

TIMESTAMPADD(interval, count, timestamp)

Add a specified interval (hour, day of week, month) to the timestamp, where intervals can have the following definition:

The interval constant may be specified either as a string literal or a constant value. Interval in {string}, count in {integer}, timestamp in {date, time, timestamp}

TIMESTAMPDIFF(interval, startTime, endTime)

Calculate the approximate number of whole intervals in (endTime - startTime) using a specific interval type (as defined by the constants in TIMESTAMPADD). If (endTime > startTime), a positive number will be returned. If (endTime < startTime), a negative number will be returned. Calculations are approximate and may be less accurate over longer time spans.

Interval in {string}; startTime, endTime in {timestamp}, returns a long.

WEEK(x)

Return week in year

x in {date, timestamp}, returns integer

YEAR(x)

Return four-digit year

x in {date, timestamp}, returns integer

CONVERT(x, type)

Convert x to type, where type is a Teiid Base Type

CAST(x AS type)

Convert x to type, where type is a Teiid Base Type

Function

Definition

Datatype Constraint

COALESCE(x,y+)

Returns the first non-null parameter

x and all y's can be any compatible types

IFNULL(x,y)

If x is null, return y; else return x

x, y, and the return type must be the same type but can be any type

NVL(x,y)

If x is null, return y; else return x

x, y, and the return type must be the same type but can be any type

NULLIF(param1, param2)

Equivalent to case when (param1 = param2) then null else param1

param1 and param2 must be compatable comparable types

DECODESTRING(x, y)

Decode column x using string of value pairs y and return the decoded column as a string

all string

DECODESTRING(x, y, z)

Decode column x using string of value pairs y with delimiter z and return the decoded column as a string

all string

DECODEINTEGER(x, y)

Decode column x using string of value pairs y and return the decoded column as an integer

all string parameters, return integer

DECODEINTEGER(x,y,z)

Decode column x using string of value pairs y with delimiter z and return the decoded column as an integer

all string parameters, return integer

COMMANDPAYLOAD()

Retrieve the string form of the command payload or null if no command payload was specified. The command payload is set by a method on the Teiid JDBC API extensions on a per-query basis.

Returns a string

COMMANDPAYLOAD(key)

Cast the command payload object to a java.util.Properties object and look up the specified key in the object

key in {string}, return is string

ENV(key)

Retrieve a system environment property. The only key specific to the current session is ‘sessionid’. However the preferred mechanism for getting the session id is with the session_id() function. To prevent untrusted access to system properties, the use of this function must be specifically enabled in the <jboss-install>/server/<profile>/deploy/teiid/teiid-jboss-beans.xml file.

key in {string}, return is string

SESSION_ID()

Retrieve the string form of the current session id.

return is string

USER()

Retrieve the name of the user executing the query

return is string

CURRENT_DATABASE()

Retrieve the catalog name of the database. Currently VDB's name is also the catalog name.

return is string