## Chapter 4. Scalar Functions

4.1. Numeric Functions
4.1.1. Parsing Numeric Datatypes from Strings
4.1.2. Formatting Numeric Datatypes as Strings
4.2. String Functions
4.3. Date/Time Functions
4.3.1. Parsing Date Datatypes from Strings
4.3.2. Specifying Time Zones
4.4. Type Conversion Functions
4.5. Choice Functions
4.6. Decode Functions
4.7. Lookup Function
4.7.1. Clearing the Cache
4.8. System Functions
4.9. XML Functions
4.10. Security Functions
4.11. User Defined Functions
4.11.1. UDF Definition
4.11.2. Source Supported UDF
4.11.3. Non-pushdown Support for User-Defined Functions
4.11.4. Installing user-defined functions

Teiid provides an extensive set of built-in scalar functions. See also SQL Support and Datatypes . In addition, Teiid provides the capability for user defined functions or UDFs .

## 4.1. Numeric Functions

Numeric functions return numeric values (integer, long, float, double, biginteger, bigdecimal). They generally take numeric values as inputs, though some take strings.

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}, return type is double

ASIN(x)

Arc sine of x

x in {double}, return type is double

ATAN(x)

Arc tangent of x

x in {double}, return type is double

ATAN2(x,y)

Arc tangent of x and y

x, y in {double}, 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}, return type is double

COT(x)

Cotangent of x

x in {double}, return type is double

DEGREES(x)

x in {double}, 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}, 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 {integer, long, float, double, biginteger}, if x is biginteger then return type is biginteger, else double

x in {double}, 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}, return type is double

SQRT(x)

Square root of x

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

TAN(x)

Tangent of x

x in {double}, 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

### 4.1.1. Parsing Numeric Datatypes from Strings

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

### 4.1.2. Formatting Numeric Datatypes as Strings

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'

## 4.2. String Functions

String functions generally take strings as inputs and return strings as outputs.

Unless specified, all of the arguments and return types in the following table are strings and all indexes are 1-based. The 0 index is considered to be before the start of the string.

Function

Definition

Datatype Constraint

x || y

Concatenation operator

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

ASCII(x)

Provide ASCII value of character x

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, is 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, is 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

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

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

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

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

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

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}

## 4.3. Date/Time Functions

Date and time functions return dates, times, or timestamps.

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 24-hour 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

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

1. SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)

2. SQL_TSI_SECOND - seconds

3. SQL_TSI_MINUTE - minutes

4. SQL_TSI_HOUR - hours

5. SQL_TSI_DAY - days

6. SQL_TSI_WEEK - weeks

7. SQL_TSI_MONTH - months

8. SQL_TSI_QUARTER - quarters (3 months)

9. SQL_TSI_YEAR - years

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 four-digit 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 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

FROM_UNIXTIME (unix_timestamp)

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

Unix timestamp (in seconds)

### 4.3.1. Parsing Date Datatypes from Strings

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')

## 4.4. Type Conversion Functions

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.

## 4.5. Choice Functions

Choice functions provide a way to select from two values based on some characteristic of one of the values.

 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 non-null parameter x and all y's can be any compatible types

IFNULL and NVL are aliases of each other. They are the same function.

## 4.6. Decode Functions

Decode functions allow you to have the Teiid Server examine the contents of a column in a result set and alter, or decode, the value so that your application can better use the results.

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:

For example, your application might query a table called PARTS that contains a column called IS_IN_STOCK which contains a Boolean value that you need to change into an integer for your application to process. In this case, you can use the DECODEINTEGER function to change the Boolean values to integers:

`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.

If, instead of using integers, your application requires string values, you can use the DECODESTRING function to return the string values you need:

`SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS;`

In addition to two input/output value pairs, this sample query provides a value to use if the column does not contain any of the preceding input values. If the row in the IS_IN_STOCK column does not contain true or false, the Teiid Server inserts a null into the result set.

When you use these DECODE functions, you can provide as many input/output value pairs if you want within the string. By default, the Teiid System expects a comma delimiter, but you can add a third parameter to the function call to specify a different delimiter:

`SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',’:’) FROM PartsSupplier.PARTS;`

You can use keyword null in the DECODE string as either an input value or an output value to represent a null value. However, if you need to use the literal string null as an input or output value (which means the word null appears in the column and not a null value) you can put the word in quotes: "null".

`SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS;`

If the DECODE function does not find a matching output value in the column and you have not specified a default value, the DECODE function will return the original value the Teiid Server found in that column.

## 4.7. Lookup Function

The Lookup function allows you to cache a group’s data in memory and access it through a scalar function. This caching accelerates response time to queries that use the lookup groups, known in business terminology as lookup tables or code groups.

A StatePostalCodes group used to translate postal codes to complete state names might represent an example of this type of lookup group. One element, PostalCode, represents a key element. Other groups within the model refer to this two-letter code. A second element, StateDisplayName, would represent the complete name of the state. Hence, a query to this lookup group would typically provide the PostalCode and expect the StateDisplayName in response.

When you call this function for any combination of codeGroup, returnElement, and keyElement for the first time, the Teiid System caches the result. The Teiid System uses this cached map for all queries, in all sessions, that later access this lookup group. The codeGroup requires use of the fully-qualified name, and the returnElement and keyElement parameters should use shortened column names.

Because the Teiid System caches and indexes this information in memory, this function provides quick access after the Teiid System initially caches the lookup group. The Teiid System unloads these cached lookup groups when you stop and restart the Teiid System. Thus, you should not use this function for data that is subject to updates. Instead, you can use it against static data that does not change over time.

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 fully-qualified 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.

## 4.8. System Functions

System functions provide access to information in the Teiid system from within a query.

Function

Definition

Datatype Constraint

`USER()`

Retrieve the name of the user executing the query

return is string

`ENV(key)`

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

key in {string}, return is string

`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

## 4.9. XML Functions

XML functions provide functionality for working with XML data.

Function

Definition

Datatype Constraint

`XPATHVALUE(doc, xpath)`

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 non-text nodes.

Doc in {string, xml} and xpath in {string}. Return value is a string.

## 4.10. Security Functions

Security functions provide the ability to interact with the security system.

Function

Definition

Datatype Constraint

`hasRole(roleType, roleName)`

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.