JBoss.orgCommunity Documentation
Teiid provides an extensive set of builtin scalar functions. See also SQL Support and Datatypes . In addition, Teiid provides the capability for user defined functions or UDFs .
Function 
Definition 
Datatype Constraint 

+  * / 
Standard numeric operators 
x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x 
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 
Teiid offers a set of functions you can use to parse numbers from strings. For each string, you need to provide the formatting of the string. These functions use the convention established by the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats by visiting the Sun Java Web site at the following URL for Sun Java.
For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to parse strings and return the datatype you need:
Input String 
Function Call to Format String 
Output Value 
Output Datatype 

'$25.30' 
parseDouble(cost, '$#,##0.00;($#,##0.00)') 
25.3 
double 
'25%' 
parseFloat(percent, '#,##0%') 
25 
float 
'2,534.1' 
parseFloat(total, '#,##0.###;#,##0.###') 
2534.1 
float 
'1.234E3' 
parseLong(amt, '0.###E0') 
1234 
long 
'1,234,567' 
parseInteger(total, '#,##0;#,##0') 
1234567 
integer 
Teiid offers a set of functions you can use to convert numeric datatypes into strings. For each string, you need to provide the formatting. These functions use the convention established within the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats by visiting the Sun Java Web site at the following URL for Sun Java .
For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to format the numeric datatypes into strings:
Input Value 
Input Datatype 
Function Call to Format String 
Output String 

25.3 
double 
formatDouble(cost, '$#,##0.00;($#,##0.00)') 
'$25.30' 
25 
float 
formatFloat(percent, '#,##0%') 
'25%' 
2534.1 
float 
formatFloat(total, '#,##0.###;#,##0.###') 
'2,534.1' 
1234 
long 
formatLong(amt, '0.###E0') 
'1.234E3' 
1234567 
integer 
formatInteger(total, '#,##0;#,##0') 
'1,234,567' 
String functions generally take strings as inputs and return strings as outputs.
Function 
Definition 
Datatype Constraint 

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. 
return type is integer 
CHR(x) CHAR(x) 
Provide the character for ASCII value x 
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 nonANSI 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 {string}, 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 white space 
x in {string}, return string 
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 {string}, 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 white space 
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} 
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} 
Date and time functions return or operate on dates, times, or timestamps.
Parse and format Date/Time functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines formats by visiting the Sun Java Web site at the following URL for Sun Java.
Function 
Definition 
Datatype Constraint 

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 
HOUR(x) 
Return hour (in military 24hour format) 
x in {time, timestamp}, returns integer 
MINUTE(x) 
Return minute 
x in {time, timestamp}, returns integer 
MONTH(x) 
Return month 
x in {date, timestamp}, returns integer 
MONTHNAME(x) 
Return name of month 
x in {date, timestamp}, returns string 
QUARTER(x) 
Return quarter 
x in {date, timestamp}, returns integer 
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 
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 {date, time, timestamp}, returns a long. 
WEEK(x) 
Return week in year 
x in {date, timestamp}, returns integer 
YEAR(x) 
Return fourdigit year 
x in {date, 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 GMT6, then modifytimezone({ts '20060110 04:00:00.0'},'GMT7', 'GMT8') will return the timestamp {ts '20060110 05:00:00.0'} as read in GMT6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT7 and GMT8. 
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 
FROM_UNIXTIME (unix_timestamp) 
Return the Unix timestamp (in seconds) as a Timestamp value 
Unix timestamp (in seconds) 
Teiid does not implicitly convert strings that contain dates presented in different formats, such as ‘19970101’ and ‘31/1/1996’ to daterelated datatypes. You can, however, use the parseDate, parseTime, and parseTimestamp functions, described in the next section, to explicitly convert strings with a different format to the appropriate datatype. These functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines date and time string formats by visiting the Sun Java Web site .
For example, you could use these function calls, with the formatting string that adheres to the java.text.SimpleDateFormat convention, to parse strings and return the datatype you need:
String 
Function Call To Parse String 

'1997010' 
parseDate(myDateString, 'yyyyMMdd') 
'31/1/1996' 
parseDate(myDateString, 'dd''/''MM''/''yyyy') 
'22:08:56 CST' 
parseTime (myTime, 'HH:mm:ss z') 
'03.24.2003 at 06:14:32' 
parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss') 
Within your queries, you can convert between datatypes using the CONVERT or CAST keyword. See also Data Type Conversions .
Function 
Definition 

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 
These functions are identical other than syntax; CAST is the standard SQL syntax, CONVERT is the standard JDBC/ODBC syntax.
Function 
Definition 
Datatype Constraint 
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 
COALESCE(x,y+) 
Returns the first nonnull parameter 
x and all y's can be any compatible types 
IFNULL and NVL are aliases of each other. They are the same function.
Function 
Definition 
Datatype Constraint 

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 
Within each function call, you include the following arguments:
SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS;
When the Teiid System encounters the value false in the result set, it replaces the value with 0.
SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS;
SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',’:’) FROM PartsSupplier.PARTS;
SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS;
Function 
Definition 
Datatype Constraint 

LOOKUP(codeGroup, returnElement, keyElement, keyValue) 
In the lookup group codeGroup, find the row where keyElement has the value keyValue and return the associated returnElement 
codeGroup must be a fullyqualified string literal containing metadata identifiers, keyValue datatype must match datatype of the keyElement, return datatype matches that of returnElement. returnElement and keyElement parameters should use their shortened names. 
System functions provide access to information in the Teiid system from within a query.
Function 
Definition 
Datatype Constraint 


Retrieve the name of the user executing the query 
return is string 

Retrieve an environment property. The only key currently allowed is ‘sessionid’, although this will expand in the future. 
key in {string}, return is string 

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 perquery basis. 
Returns a string 

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 
XML functions provide functionality for working with XML data.
Function 
Definition 
Datatype Constraint 


Takes a document and an XPATH query and returns a string value for the result. An attempt is made to provide a meaningful result for nontext nodes. 
Doc in {string, xml} and xpath in {string}. Return value is a string. 
Security functions provide the ability to interact with the security system.
Function 
Definition 
Datatype Constraint 


Whether the current caller has the role roleName of roleType. 
roleType must be one of ('data','admin' , 'repository') and roleName must be a string, the return type is Boolean. 
The following are used to define a UDF.
Function Name When you create the function name, keep these requirements in mind:
Input Parameters  defines a type specific signature list. All arguments are considered required.
Return Type  the expected type of the returned scalar value.
Deterministic  if the method will always return the same result for the same input parameters.
For example, suppose you want to use the Oraclespecific functions score and contains:
The java class containing the function method must be defined public.
Number of input arguments must match the function metadata defined in section Install userdefined functions
Any exception can be thrown, but Teiid will rethrow the exception as a FunctionExecutionException
.