Login
Register
Actian.comNavigation
Learn About
Developing With
Ingres Talk
Information
Toolbox
Views
Function mappings
From Ingres Community Wiki
Jump to: navigation, search
[edit]
Oracle to Ingres Function Mappings
The functions in bold are those common in Oracle, MySQL and SQL Server, but not in Ingres.
| Oracle Function | Ingres Function | Oracle Function Description | Comments |
| ASCII(str) | N/A | Returns the decimal representation in the database character set of the first character of str. | Ingres has ASCII function but it returns the character equivalent of str, which is an expression of either character or numeric type. |
| ADD_MONTHS(date, integer) | DATE('date') + DATE('integer months') | Returns the date 'date' plus 'integer' months. | - |
| CONVERT(char, dest_char_set[, source_char_set ]) | N/A | Converts a character string from one character set to another. | No alternative found. |
| COSH(n) | N/A | - | No alternative found. |
| CURRENT_DATE | DATE('today') | - | - |
| INITCAP(STRING_LIT) / NLS_INITCAP(char [, 'nlsparam' ]) | See here for a substitute INITCAP function in Ingres. | Gives uppercase to the title (Title case). 'nlsparam' specifies a sort sequence to be used. | NLS_INITCAP does not have an exact equivalent in Ingres. |
| INSTR | See here for a substitute CHARINDEX function in Ingres. | Used to find the location of a sub string or a character inside a string. | - |
| LAST_DAY(date) | DATE_TRUNC('MONTH', DATE(date) + '1 MONTH') - DATE('1 DAYS') | Returns the date of the last day of the month that contains date. | - |
| LTRIM(char ) | LTRIM(expr) | LTRIM removes all blanks from the left end of char. | - |
| LTRIM(char, set ) | N/A | LTRIM removes from the left end of char all of the characters contained in set. | - |
| NANVL(n2, n1) | N/A | Returns n2 if n2 is a number, else returns n1. | No alternative found. |
| NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME | N/A | Return information about the character set. | - |
| NLS_LOWER(char [, 'nlsparam' ]) NLS_UPPER(char [, 'nlsparam' ]) | LOWER(c1) UPPER(c1) | Converts all characters to lower or upper case respectively. 'nlsparam' is same as for NLS_INITCAP. | The 'nlsparam' parameter does not have an Ingres equivalent. |
| NLSSORT(char [, 'nlsparam' ]) | N/A | Returns the string of bytes used to sort char. | No alternative found. |
| NVL(expr1, expr2) | NVL | If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. | Added for Ingres 10.0. |
| NVL2(expr1, expr2, expr3) | N/A | If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. | Added for Ingres 10.0. |
| REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] ) | N/A | Extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. | - |
| REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] ) | N/A | Returns source_char with every occurrence of the regular expression pattern replaced with replace_string. | Similar to REPLACE in Ingres, but REPLACE doesn't match regular expressions, only strings. |
| REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] ] ) | N/A | Searches a string for a regular expression pattern and returns the substring. | Similar to SUBSTRING in Ingres, but SUBSTRING doesn't match regular expressions, only strings. |
| REMAINDER(n2, n1) | MOD(n, b) | Returns the remainder of n2 divided by n1. | - |
| REPLACE(str1, str2 [, str3]) | REPLACE(expr1, expr2, expr3) | Returns str1 with every occurrence of str2 replaced with str3. | str3 is optional in Oracle, but not so in Ingres. In Oracle, if str3 is null, or not provided, all occurances of str2 are removed. |
| RTRIM(char ) | RTRIM(expr) | RTRIM removes all blanks from the right end of char. | - |
| RTRIM(char, set ) | N/A | RTRIM removes from the right end of char all of the characters contained in set. | - |
| SINH(n) | N/A | - | No alternative found. |
| SUBSTR(str, position [, substring_length ]) | SUBSTR(str FROM position [FOR substring_length ]) Note: Use CASE to handle negative values for position and substr_length. | Returns substring starting at position. | - |
| SYSDATE | DATE('now') | Returns the current date and time set for the operating system on which the database resides. | - |
| TANH(n) | N/A | - | No alternative found. |
| TO_DATE(str [, fmt [, 'nlsparam' ] ]) | N/A | Converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. fmt specifies the format of str. | The DATE() function may be useful in some cases, but not all. |
| TO_NUMBER(expr [, format [, 'nlsparam' ] ]) | DECIMAL(expr [,precision[,scale]]) | Converts expression (of type BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, or NVARCHAR2 in Oracle) to a value of NUMBER datatype. Since NUMBER type corresponds to DECIMAL in Ingres, the DECIMAL function is used, to convert to DECIMAL. | - |
| TRANSLATE | N/A (replace function can be used for partial simulation) | Translate a character string. Used for encryption. | Oracle and DB/2 have different and incompatible semantics for TRANSLATE. DATAllegro submitted code (not presently public) had a function_compat config param and OME functions to emulate one or the other. |
| TREAT | N/A | Changes the declared type of an expression. | - |
| WIDTH_BUCKET(expr, min_value, max_value, num_buckets) | FLOOR(((expr* num_buckets)/(max_value - min_value)) + 1 )
| Construct equiwidth buckets, in which the bucket range is divided into intervals that have identical size. For a given expression, WIDTH_BUCKET returns the bucket number into which the value of this expression would fall after being evaluated. | - |
Retrieved from "http://community.actian.com/wiki/Function_mappings"
