FreeAdhocUDF HowTo

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'

returns a Unicode string
example: compose('o' || unistr('\0308') ) results ö

converts a string from one character set to another

accepts a string and returns a Unicode string
example: decompose('Très bien') results 'Tre`s bien'

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'

converts a char, varchar2, nchar, or nvarchar2 to a rowid

converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value

converts a LOB value from the national character set to the database character set

converts a string to an INTERVAL DAY TO SECOND type

returns a character value with all of the single-byte characters converted to multibyte characters

converts a LOB value to a NCLOB value

returns a character value with all of the multibyte characters converted to single-byte characters

converts a string to a timestamp with time zone

converts a string to an INTERVAL YEAR TO MONTH type

returns a BFILE locator for a physical LOB binary file

returns the number of elements in a nested table

assigns a number to each group resulting from a GROUP BY clause

can be used to retrieve information about the Oracle environment

returns the id number for a user's session (the user who is currently logged in)

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

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

[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.