Database Functions
The following table describes the available database functions:
Query Builder Function Name (English Localized Name)
Description
Usage
Windchill Function Name (DatastoreFunction constant)
Oracle Function or Operator Name
SQLServer Function or Operator Name
Example
Absolute
A mathematical function that returns the absolute (positive) value of the specified numeric expression.
Takes a single numeric argument expression.
ABS
ABS
ABS
Absolute(-15) returns 15
Add
A mathematical function that returns the sum of all specified numeric expressions.
Takes two or more numeric argument expressions.
ADD
"+"
"+"
Add(5, 3, 10) returns 18
Average
An aggregate function that returns the average value of the numeric expression for all rows in the results set.
Takes numeric expression as its single argument.
AVERAGE
AVG
AVG
Bit-wise And
A mathematical function that performs the bit-wise AND of two numeric expressions.
Takes two numeric expressions as parameters.
BITAND
BITAND
"&"
Bit And(5, 4) returns 4
Ceiling
A mathematical function that returns the smallest integer greater than or equal to the specified numeric expression.
Takes a single numeric argument expression.
CEIL
CEIL
CEILING
Ceiling(15.7) returns 16
Coalesce
This function returns the first non-null argument expression (in order)
Takes one or more argument expressions
COALESCE
COALESCE
COALESCE
Coalesce(null, ‘abc’) returns ‘abc’
Concatenate
A string function that returns the concatenation of all specified string expressions.
Takes one or more string argument expressions.
CONCAT
"||"
"+"
Concatenate('Wind', 'chill') returns 'Windchill'
Convert
A function that converts expressions of one data type to another data type.
Takes a data type as the first parameter, a general expression as the second parameter, and a numeric style specification as an optional third parameter.
CONVERT
Not Supported
CONVERT
Convert(varchar(1), 7) returns '7'
Count
An aggregate function that returns the number of all rows in the result set.
Takes a general expression as its single argument.
COUNT
COUNT
COUNT
Date Difference
A date function that returns the number of date and time boundaries crossed between two specified dates.
Takes a date type as the first parameter, a start date expression as the second parameter, and an end date expression as the third parameter.
DATEDIFF
Not Supported
DATEDIFF
Date Difference(week, Convert(datetime, '1/4/2006'), Convert(datetime, '1/11/2006')) returns 1
Decode
A general function that provides an equivalent behavior to a programmatic "case" statement.
Takes a general expression target as the first parameter, any number of search, value general expressions, and an optional default value general expression as the last parameter.
DECODE
DECODE
Not Supported
Decode (2, 1, 'Chicago', 2, 'New York', 3, 'New Jersey', 'Non domestic') returns 'New York'
Divide
A mathematical function that divides the first numeric expressions with the second numeric expression.
Takes two numeric expressions as parameters.
DIVIDE
"/"
"/"
Divide(12, 3) returns 4
Floor
A mathematical function that returns largest integer equal to or less than the specified numeric expression.
Takes a single numeric argument expression.
FLOOR
FLOOR
FLOOR
Floor(15.7) returns 15
Get Day
This conversion function returns the day portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_DAY
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 4
Get Hours
This conversion function returns the hours portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_HOURS
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 10
Get Minutes
This conversion function returns the minutes portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_MINUTES
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 20
Get Month
This conversion function returns the month portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_MONTH
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 1
Get Seconds
This conversion function returns the seconds portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_SECONDS
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 30
Get Year
This conversion function returns the year portion of the specified time stamp expression.
Takes a single time stamp argument expression.
GET_YEAR
TO_CHAR
DATEPART
Get Day(To Date('1/4/2006 10:20:30')) returns 2006
Hash
This function computes a hash value number.
Takes a single argument expression.
HASH
ORA_HASH
CHECKSUM
Hash(‘abc’) returns 123456
In String
This string function returns the starting position of the search string expression in a target string expression.
Takes a search string expression as the first parameter, a target string expression as the second parameter, and a starting position numeric expression as the third parameter.
IN_STRING
INSTR
CHARINDEX
In String('CORPORATE FLOOR','OR', 3) returns 5
Left Pad (with spaces)
This function returns a string expression, left-padded to the specified length with spaces.
Takes a first argument string expression and a second argument number expression.
LPAD
LPAD
Not Supported
Left Pad(‘abc’, 7) returns ‘ abc’
Length
This string function returns the length of the target string expression.
Takes a single string expression.
LENGTH
LENGTH
LEN
Length('Windchill') returns 9
Log
This mathematical function returns the logarithm of the base numeric expression of the specified numeric expression.
Takes a base numeric expression as the first parameter and a numeric expression as the second parameter.
LOG
LOG
Not Supported
Log(10, 100) returns 2
Lower
This string function returns the lower case value of the target string expression.
Takes a single string expression.
LOWER
LOWER
LOWER
Lower('Windchill') returns 'windchill'
Left Trim
This string function returns the target string expression after it removes leading blanks.
Takes a single string expression.
LTRIM
LTRIM
LTRIM
Left Trim(' Windchill') returns 'Windchill'
Maximum
An aggregate function that returns the maximum value of the numeric expression for all rows in the results set.
Takes numeric expression as its single argument.
MAXIMUM
MAX
MAX
Minimum
An aggregate function that returns the minimum value of the numeric expression for all rows in the results set.
Takes numeric expression as its single argument.
MINIMUM
MIN
MIN
Modulus
A mathematical function that returns the remainder of the second numeric expression divided by the first numeric expression.
Takes two numeric expressions as its arguments.
MOD
MOD
"%"
Modulus(11,4) returns 3
Multiply
A mathematical function that returns the product of all specified numeric expressions.
Takes two or more numeric argument expressions.
MULTIPLY
"*"
"*"
Multiply(3, 2) returns 6
NLS Sort
This string function returns a string of bytes used for locale specific sorting.
Takes a single string expression.
NLSSORT
NLSSORT
Not Supported
Null If
This function returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
Takes two argument expressions.
NULL_IF
NULLIF
NULLIF
Null If(‘xyz’,’xyz’) returns null Null If (‘abc’,’xyz’) returns ‘abc’
Null Value
This function checks the target expression and returns the default expression if it is null. Otherwise, it returns the target expression.
Takes a target expression as the first parameter and a default expression of the same type as the second parameter.
NULL_VALUE
NVL
ISNULL
Null Value(NULL, 0) returns 0
Prefix
This string function returns the first 200 characters of the string argument expression or the string argument expression if its length is 200 or less characters.
Takes a single string argument expression.
PREFIX
Windchill Database Function: WTPrefixPK.Prefix
Windchill Database Function: WtPK_prefix
Prefix(‘abc’) returns ‘abc’
Prefix (400 characters)
This string function returns the first 400 characters of the string argument expression or the string argument expression if its length is 400 or less characters.
Takes a single string argument expression.
PREFIX
Windchill Database Function: WTPrefixPK.Prefix400
Windchill Database Function: WtPK_prefix400
Prefix(‘abc’) returns ‘abc’
Reverse
This string function returns the reverse order of the string argument expression.
Takes a single string argument expression.
REVERSE
Windchill Database Function: WTReversePK.reverseChars
REVERSE
Reverse(‘abc’) returns ‘cba’
Round
A mathematical function that returns the numeric expression, rounded to the specified length or precision.
Takes a numeric expression as the first parameter and a precision numeric expression as the second parameter.
ROUND
ROUND
ROUND
Round(15.193,1) returns 15.2
Right Trim
This string function returns the target string expression after it removes trailing blanks.
Takes a single string expression.
RTRIM
RTRIM
RTRIM
Right Trim('Windchill ') returns 'Windchill'
Sign
A mathematical function that returns the sign numeric expression (-1, 0, or 1) of the specified numeric expression.
Takes a single numeric expression.
SIGN
SIGN
SIGN
Sign(-5) returns -1
Soundex
A string function that returns the phonetic representation string expression of the specified string expression.
Takes a single string expression.
SOUNDEX
SOUNDEX
SOUNDEX
Standard Deviation
An aggregate function that returns the sample standard deviation of the numeric expression for all rows in the results set.
Takes a single numeric expression.
STDDEV
STDDEV
Not Supported
Subtract
A mathematical function that subtracts the second numeric expression from the first numeric expression.
Takes two numeric expressions as parameters.
SUBTRACT
"-"
"-"
Subtract(10, 4) returns 6
Sub String
This string function returns the portion of string expression specified by the start and length numeric expressions.
Takes a string expression as the first parameter, a start numeric expression as the second parameter, and a length numeric expression as the third parameter.
SUB_STRING
SUBSTR
SUBSTRING
Sub String('Windchill', 3, 2) returns 'nd'
Sum
An aggregate function that returns the summation of the numeric expression for all rows in the results set.
Takes a single numeric expression.
SUM
SUM
SUM
System Date
This time stamp function returns the current date and time set for the operating system on which the database resides.
Takes no arguments.
SYSDATE
SYSDATE
GETDATE
Time Difference(days)
This time stamp function returns the difference in days between the first time stamp expression and the second time stamp expression.
Takes two time stamp expressions as parameters.
TIME_DIFFERENCE_IN_DAY
"-"
"/"
Time Difference(days)(To Date('1/4/2006 10:00:00'), To Date('1/2/2006 10:00:00')) returns 2
Time Difference(hours)
This time stamp function returns the difference in days between the first time stamp expression and the second time stamp expression.
Takes two time stamp expressions as parameters.
TIME_DIFFERENCE_IN_HOUR
"-"
"/"
Time Difference(days)(To Date('1/4/2006 10:00:00'), To Date('1/2/2006 10:00:00')) returns 48
To Character
This conversion function returns the string expression for the specified expression.
Takes a single expression.
TO_CHAR
TO_CHAR
CONVERT
To Character(12) returns '12'
To Date
This conversion function returns the time stamp expression for the specified expression.
Takes a single expression.
TO_DATE
TO_DATE
CONVERT
To Number
This conversion function returns the numeric expression for the specified expression.
Takes a single expression.
TO_NUMBER
TO_NUMBER
CONVERT
To Number('12') returns 12
Truncate
A mathematical function that returns the numeric expression, truncated to the specified number of decimal places.
Takes a numeric expression as the first parameter and a precision numeric expression as the second parameter.
TRUNCATE
TRUNC
Not Supported
Truncate(15.79,1) returns 15.7
Upper
This string function returns the upper case value of the target string expression.
Takes a single string expression.
UPPER
UPPER
UPPER
Upper('Windchill') returns 'WINDCHILL'
XML Exists
This function returns a Boolean true value if the traversal of the XML first argument expression using the second argument expression as an xpath query results in any nodes.
Takes an XMLType first argument expression and a string second argument expression.
XML_EXISTS
EXISTSNODE
EXIST
XML Value
This function returns a string node value after the traversal of the XML first argument expression using the second argument expression as an xpath query.
Takes an XMLType first argument expression and a string second argument expression.
XML_VALUE
XMLQUERY
VALUE
Was this helpful?