FreeAdhocUDF for InterBase and FireBird in deutschin englishen françaisen españolin italianoa português
homeprevious pageÜbersicht • overview • aperçunext pagelast page Seite empfehlen • advice website • mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
[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-functionFireBird-functionsinceFreeAdhocUDF-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 presentnot present
returns the population covariance of a set of number pairs
Covar_samp
not presentnot present
returns the sample covariance of a set of number pairs
Count
COUNT IB
Cume_Dist
not presentnot present
returns the cumulative distribution of a value in a group of values
Dense_Rank
not presentnot 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 presentnot 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 presentnot present
returns the rank of a value in a group of values. It is very similar to the dense_rank function
Remainder
not presentnot 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 presentnot 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 presentnot present
returns the population variance of a set of numbers
Var_samp
not presentnot present
returns the sample variance of a set of numbers
Variance not presentnot present
returns the variance of a set of numbers
date-/time-functions
Oracle-functionFireBird-functionsinceFreeAdhocUDF-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 presentnot 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 presentnot present
returns the first weekday that is greater than a date.
SessionTimeZone
not presentnot 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 presentnot present
returns the time zone offset of a value to UTC
converting-functions
Oracle-functionFireBird-functionsinceFreeAdhocUDF-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 presentnot 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-functionFireBird-functionsinceFreeAdhocUDF-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 presentnot present
assigns a number to each group resulting from a GROUP BY clause
Lag
not presentnot 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 presentnot 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 presentnot 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 presentnot 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
COALESCEFB 1.5SNVL2, 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
nach oben • go top • vers le hautnächste Seite • next page • prochain site