for InterBase and
FireBird
|
for InterBase and
FireBird |
|
|
|
|
|
| [HowTo] From Oracle to FireBird - comparison of functions | ||
 |
||
| Oracle-functions are up to version 11g If there is more than one possible function in FireBird and FreeAdhocUDF, the recommended functions are green represented. To equal functionality the build-in-functions are preffered - in cause of security and speed. |
||
| String-functions | |||
| Oracle-function | FireBird-function | since | FreeAdhocUDF-function |
| Ascii | ASCII_VAL only for ASCII-charset |
FB 2.1 | F_ORD only ASCII U_ORD also UTF8 |
| AsciiStr | not present - superfluous - special in Oracle | ||
|
converts a string in any character set to an ASCII string using the database character set
example: asciistr('A B C Ä Ê') results 'A B C \00C4 \00CA' |
|||
| Chr | ASCII_CHAR only for ASCII-charset |
FB 2.1 | F_CHR only ASCII U_CHAR also UTF8 |
| Compose | not present - superfluous - special in Oracle | ||
|
returns a Unicode string
example: compose('o' || unistr('\0308') ) results ö |
|||
| Concat | || (DoublePipe) | IB | |
| Concat with || | || (DoublePipe) | IB | |
| Convert | not present - superfluous - special in Oracle | ||
|
converts a string from one character set to another
|
|||
| Decompose | not present - superfluous - special in Oracle | ||
|
accepts a string and returns a Unicode string
example: decompose('Très bien') results 'Tre`s bien' |
|||
| Dump | not present - superfluous - special in Oracle | ||
|
returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
example: dump('Tech') results 'Typ=96 Len=4: 84,101,99,104' |
|||
| Initcap | not present | F_PROPERCASE only ASCII U_PROPERCASE also UTF8 |
|
| Instr | POSITION also usable with BLObs, all charsets |
FB 2.1 | F_SUBSTR only ASCII U_SUBSTR also UTF8 |
| in oracle you can chose also further appearances, in FB and FAU only the 1st appearance |
|||
| Length | CHAR_LENGTH also usable with BLObs, all charsets |
FB 2.0 FB 2.1 |
F_STRINGLENGTH only ASCII U_STRINGLENGTH also UTF8 |
| Lower | LOWER also usable with BLObs, all charsets |
FB 2.0 FB 2.1 |
F_LOWER only ASCII U_LOWER also UTF8 |
| Lpad | LPAD also usable with BLObs, all charsets |
FB 2.1 |
F_PADLEFT only ASCII U_PADLEFT also UTF8 |
| Ltrim | TRIM also usable with BLObs, all charsets trims like Oracle also other chars than spaces trims left and/or right - Oracle only left |
FB 2.0 FB 2.1 |
F_LTRIM also UTF8 trims only spaces |
| Replace | REPLACE also usable with BLObs, all charsets |
FB 2.1 | F_CONVERTSYMBOLS only ASCII U_CONVERTSYMBOLS also UTF8 |
| Rpad | RPAD also usable with BLObs, all charsets |
FB 2.1 | F_PADRIGHT only ASCII U_PADRIGHT also UTF8 |
| Rtrim | TRIM also usable with BLObs, all charsetss trims like Oracle also other chars than spaces trims left and/or right - Oracle only right |
FB 2.0 FB 2.1 |
F_RTRIM also UTF8 trims only spaces |
| Soundex | not present | F_SOUNDEX also UTF8 | |
| Substr | SUBSTRING also usable with BLObs, all charsets |
FB 2.0 FB 2.1 |
F_MID only ASCII U_MID also UTF8 |
| Translate | not present | F_CONVERTSYMBOLS only ASCII U_CONVERTSYMBOLS also UTF8 |
|
| Trim | TRIM also usable with BLObs, all charsets trims like Oracle also other chars than spaces |
FB 2.0 FB 2.1 |
F_LRTRIM also UTF8 trims only spaces |
| Upper | UPPER also usable with BLObs, all charsets |
FB 2.0 FB 2.1 |
F_UPPER only ASCII U_UPPER also UTF8 |
| VSize | not present | not present | |
| returns the number of bytes in the internal representation of an expression | |||
| Numeric-functions | |||
| Oracle-function | FireBird-function | since | FreeAdhocUDF-function |
| Abs | ABS | FB 2.1 | F_ABS, F_DOUBLEABS, F_INTEGERABS |
| Acos |
ACOS | FB 2.1 | F_ACOS |
| Asin |
ASIN | FB 2.1 | F_ASIN |
| Atan |
ATAN | FB 2.1 | F_ATAN |
| Atan2 |
ATAN2 | FB 2.1 | F_ATAN2 |
| Avg |
AVG | IB | |
| Bin_To_Num |
not present | F_CONVERTFROMBASE different Syntax, more number systems |
|
| BitAnd |
BIN_AND | FB 2.1 | F_BIN_AND |
| Ceil |
CEILING | FB 2.1 | F_CEILING |
| Corr |
not present | not present | |
| returns the coefficient of correlation of a set of number pairs | |||
| Cos |
COS | FB 2.1 | F_COS |
| Cosh |
COSH | FB 2.1 | F_COSH |
| Covar_pop |
not present | not present | |
| returns the population covariance of a set of number pairs | |||
| Covar_samp |
not present | not present | |
| returns the sample covariance of a set of number pairs | |||
| Count |
COUNT | IB | |
| Cume_Dist |
not present | not present | |
| returns the cumulative distribution of a value in a group of values | |||
| Dense_Rank |
not present | not present | |
| returns the rank of a row in a group of rows. It is very similar to the rank function | |||
| Exp |
EXP | FB 2.1 | F_EXP |
| Floor |
FLOOR | FB 2.1 | F_FLOOR |
| Greatest |
MAXVALUE | ||
| Least |
MINVALUE | ||
| Ln |
LN | FB 2.1 | F_LN |
| Log |
LOG | FB 2.1 | F_LOG |
| Max |
MAX | IB | F_MAX, F_MAXNUM |
| Median |
not present | not present | |
| returns the median of an expression | |||
| Min |
MIN | IB | F_MIN, F_MINNUM |
| Mod |
MODULO | FB 2.1 | F_MODULO |
| Power |
POWER | FB 2.1 | F_POWER |
| Rank |
not present | not present | |
| returns the rank of a value in a group of values. It is very similar to the dense_rank function | |||
| Remainder |
not present | not present | |
| returns the remainder of m divided by n. m and n could also be DOUBLE PRECISION. The remainder function uses the round function in its formula, whereas the mod function uses the floor function in its formula |
|||
| Round (numbers) |
ROUND | F_ZAHLRUNDEN, F_ROUNDCOMMON | |
| Sign |
SIGN | FB 2.1 | F_SIGN |
| Sin |
SIN | FB 2.1 | F_SIN |
| Sinh |
SINH | FB 2.1 | F_SINH |
| Sqrt |
SQRT | FB 2.1 | F_SQRT |
| StdDev |
not present | not present | |
| returns the standard deviation of a set of numbers | |||
| Sum |
SUM | IB | |
| Tan |
TAN | FB 2.1 | F_TAN |
| Tanh |
TANH | FB 2.1 | F_TANH |
| Trunc (numbers) |
TRUNC | FB 2.1 | F_TRUNCATE |
| Var_pop |
not present | not present | |
| returns the population variance of a set of numbers | |||
| Var_samp |
not present | not present | |
| returns the sample variance of a set of numbers | |||
| Variance | not present | not present | |
| returns the variance of a set of numbers | |||
| date-/time-functions | |||
| Oracle-function | FireBird-function | since | FreeAdhocUDF-function |
| Add_Months | DATEADD | FB 2.1 | F_ADDMONTH |
| Current_Date |
superfluous - datatype not present in FireBird | ||
| returns the current date with the time zone of the current SQL session as set by the ALTER SESSION command. | |||
| Current_Timestamp |
superfluous - datatype not present in FireBird | ||
| returns the current date and time with the time zone of the current SQL session as set by the ALTER SESSION command. | |||
| DbTimeZone |
not present - superfluous - special in Oracle | ||
| returns the database time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name | |||
| Extract |
EXTRACT | IB | diverse functions |
| Last_Day |
not present | F_LASTDAY different syntax F_LASTDAYMONTH |
|
| Round (dates) | not present | not present | |
| returns a date rounded to a specific unit of measure | |||
| LocalTimestamp |
CURRENT_TIMESTAMP | IB | |
| Months_Between |
DATE_DIFF | FB 2.1 | F_AGEINMONTH different syntax F_MONTHBETWEEN diff. syntax |
| Next_Day |
not present | not present | |
| returns the first weekday that is greater than a date. | |||
| SessionTimeZone |
not present | not present | |
| returns the current session's time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name | |||
| Sysdate |
CURRENT_TIMESTAMP | IB | |
| SysTimestamp |
CURRENT_TIMESTAMP | IB | |
| To_Char |
not present | F_DATETOSTR F_DATETOSTRLANG U_DATETOSTRLANG |
|
| To_Date |
CAST(string TO TIMESTAMP) | F_STRTOTIME | |
| converts a string to a date | |||
| Trunc (dates) | not present | F_FIRSTDAYQUARTER F_FIRSTDAYMONTH F_FIRSTDAYWEEK F_FIRSTDAYWEEKISO F_FIRSTDAYWOY F_FIRSTDAYKW not all possibilities of Trunc dates |
|
| Tz_Offset | not present | not present | |
| returns the time zone offset of a value to UTC | |||
| converting-functions | |||
| Oracle-function | FireBird-function | since | FreeAdhocUDF-function |
| Bin_To_Num only binary system |
not present | F_CONVERTFROMBASE different params |
|
| Cast |
CAST | IB | |
| CharToRowid |
not present - superfluous - special in Oracle | ||
|
converts a char, varchar2, nchar, or nvarchar2 to a rowid
|
|||
| From_Tz |
superfluous - datatype not present in FireBird | ||
|
converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value
|
|||
| HexToRaw |
not present | F_HEXTOINT | |
| NumToDSInterval |
not present | F_PERIOD2SECONDS also UTF8 | |
| converts a number to an INTERVAL DAY TO SECOND literal example: NumToDSInterval(1440, 'MINUTE') (interval of 1440 minuts displayed in days) results '+01' -> 1 day FAU: F_PERIOD2SECONDS('0:0:1440:0') (interval of 1440 minuts displayed in seconds) results 86400 seconds -> 86400 seconds / 60 seconds / 60 minutes / 24 hours = 1 day |
|||
| NumToYMInterval |
not present | F_PERIOD2SECONDS also UTF8 | |
| converts a number to an INTERVAL YEAR TO MONTH literal |
|||
| New_Time |
not present | not present | |
| returns a date in time zone1 to a date in time zone2. Only US-timezones possible as param | |||
| RawToHex |
not present | F_INTOTHEX | |
| To_Clob |
not present - superfluous - special in Oracle | ||
|
converts a LOB value from the national character set to the database character set
|
|||
| To_DSInterval |
not present - superfluous - special in Oracle | ||
|
converts a string to an INTERVAL DAY TO SECOND type
|
|||
| To_Lob |
superfluous, FB handles BLObs same as strings | FB 2.1 | F_STRBLOB |
| To_Multi_Byte |
not present - superfluous - special in Oracle | ||
|
returns a character value with all of the single-byte characters converted to multibyte characters
|
|||
| To_NClob | not present - superfluous - special in Oracle | ||
|
converts a LOB value to a NCLOB value
|
|||
| To_Number |
not present | F_DIGITS, F_TELEFONNR in combination with CAST() | |
| converts a string to a number and uses pattern | |||
| To_Single_Byte |
not present - superfluous - special in Oracle | ||
|
returns a character value with all of the multibyte characters converted to single-byte characters
|
|||
| To_Timestamp |
CAST(string TO TIMESTAMP) | F_STRTOTIME | |
| converts a string to a timestamp using a pattern | |||
| To_Timestamp_Tz |
not present - superfluous - special in Oracle | ||
|
converts a string to a timestamp with time zone
|
|||
| To_YMInterval | not present - superfluous - special in Oracle | ||
|
converts a string to an INTERVAL YEAR TO MONTH type
|
|||
| Advanced/diverse-functions | |||
| Oracle-function | FireBird-function | since | FreeAdhocUDF-function |
| BFilename | not present - superfluous - special in Oracle | ||
|
returns a BFILE locator for a physical LOB binary file
|
|||
| Cardinality |
not present - superfluous - special in Oracle | ||
|
returns the number of elements in a nested table
|
|||
| Case Statement |
CASE | FB 1.5 | |
| Coalesce |
COALESCE | FB 1.5 | |
| Decode |
DECODE | FB 2.1 | |
| Group_ID |
not present | not present | |
|
assigns a number to each group resulting from a GROUP BY clause
| |||
| Lag |
not present | not present | |
| an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table | |||
| Lead |
not present | not present | |
| an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table | |||
| LNNVL |
not present | not present | |
| is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value example: select * from products where LNNVL(qty >= reorder_level); | |||
| NANVL |
not present | not present | |
| lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered | |||
| NULLIF |
NULLIF | FB 1.5 | |
| NVL |
COALESCE | FB 1.5 | SNVL, INVL, DNVL |
| NVL2 |
COALESCE | FB 1.5 | SNVL2, INVL2, DNVL2 |
| Sys_Context |
not present - superfluous - special in Oracle | ||
|
can be used to retrieve information about the Oracle environment
| |||
| Uid |
not present - superfluous - special in Oracle | ||
|
returns the id number for a user's session (the user who is currently logged in)
| |||
| User |
CURRENT_USER | ||
| UserEnv | not present - superfluous - special in Oracle | ||
|
can be used to retrieve information
about the current Oracle session. Although this function still exists
in Oracle for backwards compatibility, it is recommended that you use
the sys_context function instead
| |||
 |