The Entity Provider supports a set of functions called Namespace functions, which support:
These Namespace functions are only supported in Entity SQL. The function name must be pre-appended with the Teradata namespace when used in Entity SQL queries. A query will fail if the Teradata namespace is not specified because the default namespace is Edm.
The following example demonstrates how Teradata namespace REGEXP_SUBSTR function, can be used in an Entity SQL query:
| Entity SQL Command |
Copy Code |
|---|---|
SELECT e.CompanyName, Teradata.REGEXP_SUBSTR(e.CompanyName, 'Fran(c|k)') FROM NorthwindEntities.Customers AS e order by e.customerId | |
| Function Name | Function Description | Arguments | Return Value |
|---|---|---|---|
| Microsecond (expression) | Returns the microsecond portion of expression as an Int32 between 0 - 999999 | expression: DateTime, Time or DateTimeOffset | Int32 |
| Soundex (StringArg) (note 1) |
Returns a string that represents a Soundex code for the StringArgument | StringArg: String | String |
| Char2hexint (StringArg) (note 1) |
Returns the hexadecimal representation of StringArgument | StringArg: String | String |
Please see the Teradata Functions, Operators, Expressions and Predicates manual for more information.
| Arithmetic Functions | Function Description | Arguments | Return Value |
|---|---|---|---|
| PowerEx (base,exponent) (note 2) |
Returns base raised to the power of exponent. | base: Double or Decimal exponent: Double or Decimal |
Double |
| Sign (value) (note 1) |
Returns the sign of value. | value: Int16, Int32, Int64, Decimal or Double | Double |
| Trunc (value, places) (note 1) |
Returns value truncated places to the right or left of the decimal point. | value: Int16, Int32, Int64, Decimal or Double places: Decimal or Double |
The same type as value |
| Round (value, places) (note 1) |
Returns value rounded places to the right or left of the decimal point. | value: Int16, Int32, Int64, Decimal or Double places: Decimal or Double |
The same type as value |
| String Functions | Function Description | Arguments | Return Value |
| Ascii (arg) (note 1) |
Returns the decimal representation of the first character in arg as a NUMBER. | arg: String | Double |
| Chr (numeric_exp) (note 1) |
Returns the Latin ASCII character given a numeric code value. | numeric_exp: Double or Decimal | String |
| Length (string_arg) (note 1) |
Returns the number of characters in the string argument as a NUMBER. | string_arg: String | Double |
| InitCap (string_arg) (note 1) |
Modifies a string argument and returns the string with the first character in each word in uppercase and all other characters in lowercase. | string_arg: String | String |
| LPad (source, len, [fill]) (note 1) |
Returns the source padded to the left with the characters in fill so that the resulting string is len characters. | source: String len: Int64 or Decimal fill: String |
String |
| RPad (source, len, [fill]) (note 1) |
Returns the source padded to the right with the characters in fill so that the resulting string is len characters. | source: String len: Int64 or Decimal fill: String |
String |
| LTrim (arg1, [arg2]) (note 1) |
Returns the string argument arg1, with its left-most characters removed up to the first character that is not in the string argument arg2. | arg1, arg2: String | String |
| RTrim (arg1, [arg2]) (note 1) |
Returns the string argument arg1, with its right-most characters removed up to the first character that is not in the string argument arg2. | arg1, arg2: String | String |
| OReplace (arg1, arg2, arg3) (note 1) |
Replaces every occurrence of arg2 in arg1 with arg3. | arg1, arg2, arg3: String | String |
| Instr (arg1, arg2, [pos], [occ]) (note 1) |
Searches the source_string argument for occurrences of search_string. | arg1, arg2: String pos: Int64 or Decimal occ: Int64 or Decimal |
Double |
| OTranslate (arg1, arg2, arg3) (note 1) |
Returns arg1 with every occurrence of each character in arg2 replaced with the corresponding character in arg3. | arg1, arg2, arg3: String | String |
| NGram (str1, str2, len, [pos]) (note 3) |
Returns the number of n-gram matches between str1 and str2. | str1, str2: String len: Int32 pos: Int32 |
Int64 |
| EditDistance (str1, str2, [ci, cd, cs, ct]) (note 1) |
Returns the minimum number of edit operations (insertions, deletions, substitutions and transpositions) required to transform str1 into str2. | str1, str2: String ci, cd, cs, ct: Int32 |
Int32 |
| Strtok (str1, str2, tokennum) (note 1) |
Splits str1 into tokens based on the specified list of delimiter characters and returns the nth token, where n is specified by the tokennum argument. | str1, str2: String tokennum: Int32 |
String |
| Data Type Conversion Functions | Function Description | Arguments | Return Value |
| To_Char (arg1, [formatarg], [nlsarg]) (note 1) |
Converts numeric arg1 to a character string. | arg1: Decimal or Double formatarg: String nlsarg: String |
String |
| To_Char (arg1, [formatarg]) (note 1) |
Converts DateTime arg1 to a character string. | arg1: String, time, DateTime or DateTimeOffset formatarg: String |
String |
| To_Number (str1, [formatarg], [nlsarg]) (note 1) |
Converts str1 to a NUMBER. | str1: String formatarg: String nlsarg: String |
Double |
| NumToDSInterval (numeric_value, interval_unit) (note 1) |
Converts numeric_value into an INTERVAL DAY(4) TO SECOND(6) value. | numeric_value: Decimal or Double interval_unit: String |
Time |
| NumToYMInterval (numeric_value, interval_unit) (note 1) |
Converts numeric_value into an INTERVAL YEAR(4) TO MONTH value. | numeric_value: Decimal or Double interval_unit: String |
String |
| To_DSInterval (string_value) (note 1) |
Converts string_value into an INTERVAL DAY(4) TO SECOND(6) value. | string_value: String | Time |
| To_YMInterval (string_value) (note 1) |
Converts string_value into an INTERVAL YEAR(4) TO MONTH value. | string_value: String | String |
| To_Date (string_exp, format_arg) (note 1) |
Converts string_expr to a DATE data type. | string_exp: String format_arg: String |
DateTime |
| To_Timestamp (string_exp, format_arg) (note 1) |
Converts string_expr to a TIMESTAMP data type. | string_exp: String format_arg: String |
DateTime |
| To_Timestamp_Tz (string_exp, [format_arg]) (note 1) |
Converts string_expr to a TIMESTAMP WITH TIME ZONE data type. | string_exp: String format_arg: String |
DateTimeOffset |
| DateTime and Interval Functions | Function Description | Arguments | Return Value |
| Last_Day (date_timestamp_value) (note 1) |
Returns the date of the last day of the month that contains date_timestamp_value. | date_timestamp_value: DateTime or DateTimeOffset | DateTime |
| Next_Day (date_timestamp_value, day_value) (note 1) |
Returns the date of the first weekday (day_value) that is later than the date specified by date_timestamp_value. | date_timestamp_value: DateTime or DateTimeOffset day_value: String |
DateTime |
| Months_Between (dt_value1, dt_value2) (note 1) |
Returns the number of months between dt_value1 and dt_value2. | dt_value1, dt_value2: DateTime or DateTimeOffset | Double |
| OAdd_Months (dt_value, num_months) (note 1) |
Adds a specified dt_value to a specified num_months and returns the resulting date. | dt_value: DateTime or DateTimeOffset num_months: Int32 |
DateTime |
| Trunc (dt_value, [fmt]) (note 1) |
Returns dt_value with the time portion of the day truncated to the unit specified by fmt. | dt_value: DateTime or DateTimeOffset fmt: String |
DateTime |
| Round (dt_value, [fmt]) (note 1) |
Returns dt_value with the time portion of the day rounded to the unit specified by fmt. | dt_value: DateTime or DateTimeOffset fmt: String |
DateTime |
| Null Handling Functions | Function Description | Arguments | Return Value |
| NVL (expr1, expr2) (note 1) |
Replaces a NULL with a numeric or a string value as the result value. | expr1: String, Decimal, Double, Int64, Int32, Int16 expr2: String, Decimal, Double, Int64, Int32, Int16 |
Depending upon expr1 and expr2 return value can be a String, Decimal, Double, Int64, Int32, Int16 |
| NVL2 (expr1, expr2, expr3) (note 1) |
Returns one of two values based on whether or not expr1 is NULL. | expr1: String, Decimal, Double expr2: String, Decimal, Double, Int64, Int32, Int16 expr3: String, Decimal, Double, Int64, Int32, Int16 |
Depending upon expr1 , expr2 and expr3 return value can be a String, Decimal, Double, Int64, Int32, Int16 |
| Regular Expression Functions | Function Description | Arguments | Return Value |
| RegExp_Substr (srcexpr, regexpr, [pos], [occ], [match]) (note 1) |
Extracts a substring from srcexpr that matches a regular expression specified by regexpr. | srcexpr: String regexpr: String pos: Decimal or Double occ: Decimal or Double match: String |
String |
| RegExp_Instr (srcexpr, regexpr, [pos], [occ], [retopt], [match]) (note 4) |
Searches srcexpr for a match to regexpr. | srcexpr: String regexpr: String pos: Decimal or Double occ: Decimal or Double retopt: Decimal or Double match: String |
Int64 |
| RegExp_Similar (srcexpr, regexpr, [match]) (note 1) |
Compares srcexpr to regexpr and returns an integer value. | srcexpr: String regexpr: String match: String |
Int32 |
| RegExp_Replace (srcexpr, regexpr, replexpr, [pos], [occ], [match]) (note 1) |
Replaces portions of srcexpr that match regexpr with the replexpr. | srcexpr: String regexpr: String replexpr: String pos: Decimal or Double occ: Decimal or Double match: String |
String |
| LOB Functions | Function Description | Arguments | Return Value |
| Empty_Blob () (note 1) |
Returns an empty BLOB (Binary Large Object), that is, one that contains 0 B. | Binary | |
| Empty_Clob () (note 1) |
Returns an empty CLOB (Character Large Object), that is, one that contains 0 B. | String | |
| Miscellaneous Functions | Function Description | Arguments | Return Value |
| Least (expr1, expr2 ... expr9) (note 5) |
Returns the least value among a list of numeric_values or string_values. | expr1, expr2 .... expr9: String or Decimal | The same type as that of expr1, expr2 .... expr9 |
| Greatest (expr1, expr2 ... expr9) (note 5) |
Returns the greatest value among a list of numeric_values or string_values. | expr1, expr2 .... expr9: String or Decimal | The same type as that of expr1, expr2 .... expr9 |
Please see the Teradata Functions, Operators, Expressions and Predicates manual for more information of all the Teradata Database 14.00 Functions.
The function PowerEx (base, exponent) is the Teradata Entity Data Provider implementation of the Teradata Arithmetic function Power (base, exponent). The Teradata Entity Data Provider contains an existing Teradata.Power (base, exponent) function that is implemented using the Teradata ** operator. See the Teradata Functions, Operators, Expressions and Predicates for more information on the ** operator.
Teradata string function NGram (str1, str2, len, [pos]) will always return an Int64. The Teradata Functions, Operators, Expressions and Predicates indicates the return data type will be an Int32 for str1 data type of Char or Varchar and an Int64 for a data type of CLOB.
Teradata regular expression function RegExp_Instr (srcexpr, regexpr, [pos], [occ], [retopt], [match]) will always return an Int64. The Teradata Functions, Operators, Expressions and Predicates indicates the return data type will be an Int32 for srcexpr data type of Char or Varchar and an Int64 for a data type of CLOB.
Teradata miscellaneous functions Greatest() and Least() are limited in their numeric support within the Teradata Entity Data Provider. Both functions always return a decimal data type, and are limited in the numeric data types to ByteInt, SmallInt, Integer, BigInt and Decimal. The support for both functions is not limited while the data types are strings. They support from 1 to 10 arguments as the Teradata Functions, Operators, Expressions and Predicates manual indicates, and arguments must be all strings or all numeric data types.
These Teradata namespace functions are provided as extension functions to Entity Framework 3.5. Some of these functions have the same name and provide similar functionality to the Entity Framework 4.0 Canonical functions.
| Aggregate Canonical Functions | Function Description | Arguments | Return Value |
|---|---|---|---|
| StDevP(expression) | Returns the standard deviation for the population of all values | expression: collection of Double or Decimal or Int32 or Int64 | Double |
| Var(expression) | Returns the variance of all non-null values | expression: collection of Double or Decimal or Int32 or Int64 | Double |
| VarP(expression) | Returns the variance for the population of all non-null values. | expression: collection of Double or Decimal or Int32 or Int64 | Double |
| String Canonical Functions | Function Description | Arguments | Return Value |
| Contains(string, target) | Returns true if target is contained in string | string: String target: String |
Boolean |
| EndsWidth(string, target) | Returns true if target ends with string | string: String target: String |
Boolean |
| StartsWidth(string, target) | Returns true if string starts with target | string: String target: String |
Boolean |
| Math Canonical Functions | Function Description | Arguments | Return Value |
| Power(value, exponent) | Returns the result of the specified value to the specified exponent | value: Decimal or Double exponent: Decimal or Double |
Double (note 2) |
| Date and Time Canonical Functions | Function Description | Arguments | Return Value |
| AddMicroseconds(expression, number) (note 1) |
Adds the specified number of microseconds to the expression | expression: Time or DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddMilliseconds(expression, number) (note 1) |
Adds the specified number of milliseconds to the expression | expression: Time or DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddSeconds(expression, number) (note 1) |
Adds the specified number of seconds to the expression | expression: Time or DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddMinutes(expression, number) (note 1) |
Adds the specified number of minutes to the expression | expression: Time or DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddHours(expression, number) (note 1) |
Adds the specified number of hours to the expression. | expression: Time or DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddDays(expression, number) (note 1) |
Adds the specified number of days to the expression. | expression: DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddMonths(expression, number) (note 1) |
Adds the specified number of months to the expression. | expression: DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| AddYears(expression, number) (note 1) |
Adds the specified number of years to the expression. | expression: DateTime or DateTimeOffset number: Int32 |
The same type as that of expression |
| DiffMicroseconds(startexpression, endexpression) | Returns the difference, in microseconds, between startExpression and endExpression. | startexpression, endexpression: Time or DateTime or DateTimeOffset | Int32 |
| DiffMilliseconds(startexpression, endexpression) | Returns the difference, in milliseconds, between startExpression and endExpression. | startexpression, endexpression: Time or DateTime or DateTimeOffset | Int32 |
| DiffSeconds(startexpression, endexpression) | Returns the difference, in seconds, between startExpression and endExpression. | startexpression, endexpression: Time or DateTime or DateTimeOffset | Int32 |
| DiffMinutes(startexpression, endexpression) | Returns the difference, in minutes, between startExpression and endExpression. | startexpression, endexpression: Time or DateTime or DateTimeOffset | Int32 |
| DiffHours(startexpression, endexpression) | Returns the difference, in hours, between startExpression and endExpression. | startexpression, endexpression: Time or DateTime or DateTimeOffset | Int32 |
| DiffDays(startexpression, endexpression) | Returns the difference, in days, between startExpression and endExpression | startexpression, endexpression: DateTime or DateTimeOffset | Int32 |
| DiffMonths(startexpression, endexpression) | Returns the difference, in months, between startExpression and endExpression | startexpression, endexpression: DateTime or DateTimeOffset | Int32 |
| DiffYears(startexpression, endexpression) | Returns the difference, in years, between startExpression and endExpression | startexpression, endexpression: DateTime or DateTimeOffset | Int32 |
| CreateDateTime(year, month, day, hour, minute, second) | Returns a new DateTime value as the current date and time of the server in the server's time zone | year, month, day, hour, minute: Int16 or Int32 second: Double |
DateTime |
| CreateDateTimeOffset(year, month, day, hour, minute, second, tzoffset) | Returns a new DateTimeOffset value as the current date and time of the server relative to the Coordinated Universal Time (UTC). | year, month, day, hour, minute: Int32 second: Double tzoffset: Int32 |
DateTimeOffset |
| CreateTime(hour, minute, second) | Returns a new Time value as the current time. | hour, minute: Int32 second: Double |
Time |
All DateTime Canonical Add functions use Teradata Intervals to add the value to the DateTime, DateTimeOffset or Time (when applicable). Teradata Intervals (see the Teradata Data Types and Literals manual) are limited by a maximum precision of 4, which limits the maximum value in the Canonical Add function. Precision is the number of significant digits in the most significant field of the Interval. For example in the AddMonths function, the Interval Month is used to add months to the DateTime or DateTimeOffset. A maximum value of 9999 months (four significant digits of precision) may be added to a DateTime or DateTimeOffset in this example.
The math Canonical function Power differs from the return type signatures defined by Microsoft in the .NET Framework 4.0 Canonical functions. The specification indicates a return type the same as the argument type. This may limit the result in Decimal, Int32 and even Int64 data types. For this reason the return type is a Double, allowing for the maximum result. The Power function supports argument types of Int32, Int64, Double and Decimal.