for InterBase and FireBird

Functions enclosed in FreeAdhocUDF.dll/.so/.dylib :
String-Functions		91 functions and 28 Variants
	Create	10 functions
	Edit	45 functions and 19 variants
	Convert	21 functions and 6 variants
	Compare	3 functions
	Find / Determin	12 functions and 3 variant
There are some variants for some functions, f.e. for F_LEFT the variant F_LEFT4 and F_LEFT8. The variants are only differ in the declaration of the output-string: F_LEFT   -> Input CSTRING(254), Output CSTRING (254) F_LEFT4 -> Input CSTRING(4096), Output CSTRING(4096) F_LEFT8 -> Input CSTRING(8190), Output CSTRING(8190) The variants could be used in one database at the same time but not in one SQL.
		returns <null> instead of 0, '' (empty string) or '17.11.1858'
		since FireBird 1.0 this function is substitutable with a native SQL statement
		since FireBird 2.0 this function is substitutable with a native SQL statement
		since FireBird 2.1 this function is substitutable with a native SQL statement
		Output RETURN mechanism if nothing other is published: FREE_IT TestSQLs with NULL run only in FireBird 2.

 

String-functions: Create
F_CRLF		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
Entrypoint	crlf	compatible with UTF-8
Input	without
Output	CSTRING(3)	Carriage Return and Linefeet - produce the indications CHR(13)  +  CHR(10)
TestSQL SELECT ' ABC' || F_CRLF() || '123' FROM RDB$DATABASE; returns: ‘ABC’  ( 1st row )                 ‘123’    ( 2nd row ) SELECT 'first row' || F_CRLF() || 'second row' FROM RDB$DATABASE;
F_LF		function from adhoc
Entrypoint	lf	compatible with UTF-8
Input	without
Output	CSTRING(2)	Linefeet - produce the indications CHR(10)
Identically to F_CHARACTER(10) TestSQL SELECT 'ABC' || F_LF() || '123' FROM RDB$DATABASE;
F_SPACE		compatibility to GrUDF
Entrypoint	space	compatible with UTF-8
Input	INTEGER	number of blanks
Output	CSTRING(32760)	blank chain (CHR(32)) with indicated numbers
TestSQL SELECT F_STRINGLENGTH(F_SPACE(10)) || ' Leerzeichen und ein x' AS ISCORRECT, F_SPACE(10) || 'x' FROM RDB$DATABASE;
F_NBSP		function from adhoc
Entrypoint	nbsp	not compatible with UTF-8 - use U_NBSP
U_NBSP		function from adhoc
Entrypoint	u_nbsp	compatible with UTF-8
Input	without
Output	CSTRING(2)	non-braking-space generate CHR(160) respectively U+00A0
Identically to  F_CHARACTER(160). TestSQL SELECT 'ABC 123' AS ISCORRECT, 'ABC' || F_NBSP() || '123' FROM RDB$DATABASE; SELECT 'ABC 123' AS ISCORRECT, 'ABC' || U_NBSP() || '123' FROM RDB$DATABASE;
U_NNBSP		function from adhoc
Entrypoint	u_nnbsp	only with UTF-8
Input	without
Output	CSTRING(2)	narrow-non-braking-space generate U+202F
TestSQL SELECT 'z. B. 123' AS ISCORRECT, 'z.' || F_NNBSP() || 'B. 123' FROM RDB$DATABASE;
F_DQM		function from adhoc
Entrypoint	dqm	compatible with UTF-8
Input	without
Output	CSTRING(2)	doube-quote-mark CHR(34)
Identically to F_CHARACTER(34). TestSQL SELECT 'ABC"123' AS ISCORRECT, 'ABC' || F_DQM() || '123' FROM RDB$DATABASE;
F_SQM		function from adhoc
Entrypoints	sqm	compatible with UTF-8
Input	without
Output	CSTRING(2)	single-quote-mark CHR(39)
Identically to F_CHARACTER(39). TestSQL SELECT 'ABC<einfaches Hochkoma>123' AS ISCORRECT, 'ABC' || F_SQM() || '123' FROM RDB$DATABASE;
F_DSQM		Funktion von adhoc
Entrypoint	dsqm	compatible with UTF-8
Input	without
Output	CSTRING(2)	2 single-quote-marks CHR(39)
TestSQL SELECT 'ABC<2 single quote marks>123' AS ISCORRECT, 'ABC' || F_DSQM() || '123' FROM RDB$DATABASE;
F_TAB		function from adhoc
Entrypointt	tab	compatible with UTF-8
Input	without
Output	CSTRING(2)	Tabulator CHR(9)
Identically to F_CHARACTER(9). TestSQL SELECT 'ABC<TAB>123' AS ISCORRECT, 'ABC' || F_TAB() || '123' FROM RDB$DATABASE;

 

String-Functions: Edit
from		substitutable with LEFT
F_LEFT (F_LEFT4, F_LEFT8) F_BIGLEFT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	left	not compatible with UTF-8 - use U_LEFT(U_LEFT4, U_LEFT8)
U_LEFT (U_LEFT4, U_LEFT8)		function from adhoc
Entrypoint	u_left	compatible with UTF-8
Input	CSTRING(254) INTEGER	string length of the cut-off-string
Output	CSTRING(254)	string cut on the number indication from left gives from parameter 2
Counting starts with 1 TestSQL SELECT 'Dies i' AS ISCORRECT, F_LEFT('Dies ist ein Test', 6) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_LEFT(NULL, NULL) FROM RDB$DATABASE;
from		substitutable with SUBSTRING
F_MID (F_MID4, F_MID8) F_BIGMID		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
Entrypoint	mid	not compatible with UTF-8 - use U_MID(U_MID4, U_MID8)
U_MID (U_MID4, U_MID8)		function from adhoc
Entrypoint	u_mid	compatible with UTF-8
F_COPY		compatibility to GrUDF
Entrypoint	mid1	not compatible with UTF-8 - use U_MID(U_MID4, U_MID8)
F_STRCOPY		input/output-compatibility to rFunc (SUBSTR, LONGSUBSTR)
Entrypoint	strcopy	not compatible with UTF-8 - use U_MID(U_MID4, U_MID8)
Input	CSTRING(254) INTEGER INTEGER	String, from which a character string is to be determined Startposition of the string Length of the string
Output	CSTRING(254)	string starts at position form parameter 2 with the lenght of parameter 3
For F_MID and F_STRCOPY counting of parameter 2 start with 0, for F_COPY with 1! TestSQL SELECT 'tag' AS ISCORRECT, F_MID('Geburtstagsparty', 7, 3) FROM RDB$DATABASE; SELECT 'tag' AS ISCORRECT, F_COPY('Geburtstagsparty', 8, 3) FROM RDB$DATABASE; SELECT 'tag' AS ISCORRECT, F_STRCOPY('Geburtstagsparty', 7, 3) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_MID(NULL, NULL, NULL) FROM RDB$DATABASE;
from		substitutable with RIGHT
F_RIGHT (F_RIGHT4, F_RIGHT8) F_BIGRIGHT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	right	not compatible with UTF-8 - use U_RIGHT(U_RIGHT4, U_RIGHT8)
U_RIGHT (U_RIGHT4, U_RIGHT8)		function from adhoc
Entrypoint	u_right	compatible with UTF-8
Input	CSTRING(254) INTEGER	String length of the cut-off-string
Output	CSTRING(254)	string cut on the number indication from right gives from parameter 2
Counting starts with 1 TestSQL SELECT 'n Test' AS ISCORRECT, F_RIGHT('Dies ist ein Test', 6) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_RIGHT(NULL, NULL) FROM RDB$DATABASE;
from		substitutable with POSITION
F_SUBSTR (F_SUBSTR4, F_SUBSTR8) F_BIGSUBSTR		compatibility to FreeUDFLibC
Entrypoint	substr	not compatible with UTF-8 - use U_SUBSTR
Input	CSTRING(8190) CSTRING(1024)	String 1 (in which the position of parameter 2 is to be determin) String 2 (to be determin)
Output	INTEGER	first position in string 1 where string 2 starts
F_STRSUB		compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
Entrypoint	strsub	not compatible with UTF-8 - use U_SUBSTR
F_STRPOS		input/output-compatibility to rFunc (STRPOS)
Entrypoint	strpos	not compatible with UTF-8 - use U_SUBSTR
Input	CSTRING(1024) CSTRING(8190)	String 1 (to be determin) String 2 (in which the position of parameter 2 is to be determin)
Output	INTEGER	first position in string 1 where string 2 starts
F_SUBSTR, F_STRSUB etc.: Counting starts with 0, if not found -1. F_STRPOS: Counting starts with 1, if not found 0. Notice: In the original FreeUDFLib (1998 by Gregory Deatz) the order of the input-parameters are swapped to the other string-funktions and to the c-version (1999 by Gregory Deatz) of the FreeUDFLibC. To maintain backward compatibility for both versions there are two different “entrypoints” you can use in the DECLARE-Script of F_SUBSTR. - To be compatible with the (Delphi-)FreeUDFLib, the FreeUDFLib AvERP and the GrUDF (which is compatible to the Delphi-FreeUDFLib) use the entrypoint “strsub” - for compatibility to FreeUDFLibC use entrypoint “substr”. TestSQL (Version FreeUDFLibC mit Entrypoint substr) SELECT 9 AS ISCORRECT, F_SUBSTR('Pauline fährt in Urlaub', 'ähr') FROM RDB$DATABASE; SELECT -1 AS ISCORRECT, F_SUBSTR('Pauline fährt in Urlaub', 'chr') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_SUBSTR(NULL, NULL) FROM RDB$DATABASE; SELECT 10 AS ISCORRECT, F_STRPOS('ähr', 'Pauline fährt in Urlaub') FROM RDB$DATABASE; SELECT 0 AS ISCORRECT, F_STRPOS('chr', 'Pauline fährt in Urlaub') FROM RDB$DATABASE; TestSQL (Version FreeUDFLib, GrUDF mit Entrypoint strsub) SELECT 9 AS ISCORRECT, F_SUBSTR('ähr', 'Pauline fährt in Urlaub') FROM RDB$DATABASE;
F_SUBSTRN (F_SUBSTR)		function from adhoc
Entrypoint	substrn	not compatible with UTF-8 - use U_SUBSTR
U_SUBSTR		function from adhoc
Entrypoint	u_substr	compatible with UTF-8
Input	CSTRING(8190) CSTRING(1024)	String 1 (in which the position of parameter 2 is to be determin) String 2 (to be determin)
Output	INTEGER	first position in string 1 where string 2 starts
F_STRSUBN		function from adhoc
Entrypoint	strsubn	not compatible with UTF-8 - use U_SUBSTR
Input	CSTRING(1024) CSTRING(8190)	String 1 (to be determin) String 2 (in which the position of parameter 2 is to be determin)
Output	INTEGER	first position in string 2 where string 1 starts
The formerly functions (F_SUBSTR and F_STRSUB) return -1 if the string is not found. To get a correct <null> in FireBird 2.0 (and to save the old variant with -1) use the entrypoint "strsubnull" or "substrnull". TestSQL (Version FreeUDFLibC mit Entrypoint substrnull) SELECT NULL AS ISCORRECT, F_SUBSTR('Pauline fährt in Urlaub', 'chr') FROM RDB$DATABASE;
F_STRRM		compatibility to
Entrypoint	strrm	not compatible with UTF-8 - use U_STRRM
U_STRRM		function from adhoc
Entrypoint	u_strrm	compatible with UTF-8
Input	CSTRING(8190) INTEGER	String where a character string had to be remove Place in the string, which is to be removed
Output	CSTRING(8190)	string with the removed string
counting starts with 0 TestSQL SELECT 'ies ist ein Test' AS ISCORRECT, F_STRRM('Dies ist ein Test', 0) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRRM(NULL, NULL) FROM RDB$DATABASE;
F_LTRIM (F_LTRIM4, F_LTRIM8) F_BIGLTRIM		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc ( LTRIM, LONGLTRIM)
Entrypoint	ltrim	compatible with UTF-8
Input	CSTRING(8190)	String whose leeding blanks should be removed
Output	CSTRING(8190)	String without leeding blanks
Does not remove the protected blanks (with < ALT > < 255 >) TestSQL SELECT 'Dies ist ein Test' AS ISCORRECT, F_LTRIM('   Dies ist ein Test') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_LTRIM(NULL) FROM RDB$DATABASE;
from		substitutable with TRIM
F_RTRIM (F_RTRIM4, F_RTRIM8) F_BIGRTRIM		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc ( RTRIM, LONGRTRIM)
Entrypoint	rtrim	compatible with UTF-8
Input	CSTRING(8190)	String whose trailing blanks should be removed
Output	CSTRING(8190)	String without trailing blanks
Does not remove the protected blanks (with < ALT > < 255 >) TestSQL SELECT 'Dies ist ein Test' AS ISCORRECT, F_STRINGLENGTH(F_RTRIM('Dies ist ein Test   ')) AS ANZ_ZEICHEN, F_RTRIM('Dies ist ein Test   ') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRINGLENGTH(F_RTRIM(NULL)) AS ANZ_ZEICHEN, F_RTRIM(NULL) FROM RDB$DATABASE;
F_LRTRIM (F_LRTRIM4, F_LRTRIM8) F_BIGLRTRIM		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (TRIM, LONGTRIM)
Entrypoint	lrtrim	compatible with UTF-8
Input	CSTRING(8190)	String whose leeding and trailing blanks should be removed
Output	CSTRING(8190)	String without leeding and trailing blanks
Does not remove the protected blanks (with < ALT > < 255 >) TestSQL SELECT 'Dies ist ein Test' AS ISCORRECT, F_STRINGLENGTH(F_LRTRIM('  Dies ist ein Test   ')) AS ANZ_ZEICHEN, F_LRTRIM('  Dies ist ein Test   ') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRINGLENGTH(F_LRTRIM(NULL)) AS ANZ_ZEICHEN, F_LRTRIM(NULL) FROM RDB$DATABASE;
from		substitutable with LPAD
F_PADLEFT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	padleft	not compatible with UTF-8 - use U_PADLEFT
U_PADLEFT		function from adhoc
Entrypoint	u_padleft	compatible with UTF-8
Input	CSTRING(4095) CSTRING(16) INTEGER	String 1, filled up left with the indications from string 2 to the length of parameter 3 String 2 to fill up with Length of the string, up to which is to be filled up
Output	CSTRING(4095)	String 1 left filled up from the string with the indications from string 2 to the overall length of indications of parameter 3
F_RPADLEFT		input/output-compatibility to rFunc (PADLEFT, LONGPADLEFT)
Entrypoint	r_padleft	not compatible with UTF-8 - use U_PADLEFT
Input	CSTRING(4095) INTEGER CSTRING(16)	String 1, filled up left with the indications from string 2 to the length of parameter 3 Length of the string, up to which is to be filled up String 2 to fill up with
Output	CSTRING(4095)	String 1 left filled up from the string with the indications from string 2 to the overall length of indications of parameter 3
If you enter more than one character in string 2, the filling with characters of string 2 starts from right and abort, if the required number of the complete characters will be reached. (Look at. 2. TestSQL) TestSQL SELECT 'XXXDies ist ein Test' AS ISCORRECT, F_PADLEFT('Dies ist ein Test', 'X', 20) FROM RDB$DATABASE; SELECT 'xXxDies ist ein Test' AS ISCORRECT, F_PADLEFT('Dies ist ein Test', 'Xx', 20) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_PADLEFT(NULL, NULL, NULL) FROM RDB$DATABASE;
from		substitutable with RPAD
F_PADRIGHT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	padright	not compatible with UTF-8 - use U_PADRIGHT
U_PADRIGHT		function from adhoc
Entrypoint	u_padright	compatible with UTF-8
Input	CSTRING(4095) CSTRING(16) INTEGER	String 1, filled up right with the indications from string 2 to the length of parameter 3 String 2 to fill up with Length of the string, up to which is to be filled up
Output	CSTRING(4095)	String 1 right filled up from the string with the indications from string 2 to the overall length of indications of parameter 3
F_RPADRIGHT		input/output-compatibility to rFunc (PADRIGHT, LONGPADRIGHT)
Entrypoint	r_padright	not compatible with UTF-8 - use U_PADRIGHT
Input	CSTRING(4095) INTEGER CSTRING(16)	String 1, filled up right with the indications from string 2 to the length of parameter 3 Length of the string, up to which is to be filled up String 2 to fill up with
Output	CSTRING(4095)
If you enter more than one character in string 2, the filling with characters of string 2 starts from right and abort, if the required number of the complete characters will be reached. (Look at. 2. TestSQL) TestSQL SELECT 'Dies ist ein TestXXX' AS ISCORRECT, F_PADRIGHT('Dies ist ein Test', 'X', 20) FROM RDB$DATABASE; SELECT 'Dies ist ein TestXxX' AS ISCORRECT, F_PADRIGHT('Dies ist ein Test', 'Xx', 20) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_PADRIGHT(NULL, NULL, NULL) FROM RDB$DATABASE;
F_PADCENTER		function from adhoc
Entrypoint	padcenter	not compatible with UTF-8 - use U_PADCENTER
U_PADCENTER		function from adhoc
Entrypoint	u_padcenter	compatible with UTF-8
Input	CSTRING(4095) CSTRING(16) INTEGER	String 1, filled up with the indications from string 2 to the length of parameter 3 String 2 to fill up with Length of the string, up to which is to be filled up
Output	CSTRING(4095)	String 1 left and right filled up from the string with the indications from string 2 to the overall length of indications of parameter 3
If the "filled-length" is not eval, there is one more character filled up left TestSQL SELECT 'XXDies ist ein TestXX' AS ISCORRECT, F_PADCENTER('Dies ist ein Test', 'X', 21) FROM RDB$DATABASE; SELECT 'XXDies ist ein TestX' AS ISCORRECT, F_PADCENTER('Dies ist ein Test', 'X', 20) FROM RDB$DATABASE; SELECT 'XxDies ist ein TestXx' AS ISCORRECT, F_PADCENTER('Dies ist ein Test', 'Xx', 21) FROM RDB$DATABASE; SELECT 'XxDies ist ein TestX' AS ISCORRECT, F_PADCENTER('Dies ist ein Test', 'Xx', 20) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_PADCENTER(NULL, NULL, NULL) FROM RDB$DATABASE;
F_LINEWRAP		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	linewrap	not compatible with UTF-8 - use U_LINEWRAP
U_LINEWRAP		function from adhoc
Entrypoint	u_linewrap	compatible with UTF-8
Input	CSTRING(32760) INTEGER INTEGER	String starting position measure
Output	CSTRING(32760)	Spends all words of the string, begin with the initial position, which are all not longer than the measure.
Counting starts with 0. TestSQL SELECT  'alle einer Geburtstagsparty' AS ISCORRECT, F_LINEWRAP('Wir gehen alle einer Geburtstagsparty', 10, 30) FROM RDB$DATABASE; SELECT  'alle einer' AS ISCORRECT, F_LINEWRAP('Wir gehen alle einer Geburtstagsparty', 10, 29) FROM RDB$DATABASE; SELECT  NULL AS ISCORRECT, F_LINEWRAP(NULL, NULL, NULL) FROM RDB$DATABASE;
from		substitutable with REPLACE
F_REPLACE (F_REPLACE4, ...8) F_BIGREPLACE		compatibility to FreeUDFLibC
Entrypoint	replace	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254) CSTRING(254)	string where a character string had to be exchanged old string new string
Output	CSTRING(32760)	string with replaced characters from parameter 2 with characters from parameter 3
Easy version of the funktion F_REPLACESTRING without the posibility to change the string more than one time and independent of upper and lower case. TestSQL            SELECT 'Dies ist ein Versuch zwei Versuch drei Versuch vier TEST' AS ISCORRECT, F_REPLACE('Dies ist ein Test zwei Test drei Test vier TEST', 'Test', 'Versuch') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_REPLACE(NULL, NULL, NULL) FROM RDB$DATABASE;
F_REPLACESTRING (..4, ..8) F_BIGREPLACESTRING		compatibility to  FreeUDFLib AvERP, GrUDF
Entrypoint	replacestring	not compatible with UTF-8 - use U_REPLACESTRING
U_REPLACESTRING (..4, ..8)		function from adhoc
Entrypoint	u_replacestring	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254) CSTRING(254) INTEGER INTEGER	string where a character string had to be exchanged old string new string 0 = replace only the first occurrence, 1 = replace all occurrences 0 = consider upper and lower case, 1 = not consider
Output	CSTRING(32760)	string with replaced characters from parameter 2 with characters from parameter 3
TestSQL            SELECT 'Dies ist ein Versuch zwei Test drei Test vier TEST' AS ISCORRECT, F_REPLACESTRING('Dies ist ein Test zwei Test drei Test vier TEST', 'Test', 'Versuch', 0, 0) FROM RDB$DATABASE; SELECT 'Dies ist ein Versuch zwei Test drei Test vier TEST' AS ISCORRECT, F_REPLACESTRING('Dies ist ein Test zwei Test drei Test vier TEST', 'Test', 'Versuch', 0, 1) FROM RDB$DATABASE; SELECT 'Dies ist ein Versuch zwei Versuch drei Versuch vier TEST' AS ISCORRECT, F_REPLACESTRING('Dies ist ein Test zwei Test drei Test vier TEST', 'Test', 'Versuch', 1, 0) FROM RDB$DATABASE; SELECT 'Dies ist ein Versuch zwei Versuch drei Versuch vier Versuch' AS ISCORRECT, F_REPLACESTRING('Dies ist ein Test zwei Test drei Test vier TEST', 'Test', 'Versuch', 1, 1) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_REPLACESTRING(NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
F_CONVERTSYMBOLS		input/output-compatibility to rFunc (CONVERTSYMBOLS, LONGCONVERTSYMBOLS)
Entrypoint	convertsymbols	not compatible with UTF-8 - use U_CONVERTSYMBOL
U_CONVERTSYMBOLS		function from adhoc
Entrypoint	u_convertsymbols	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254) CSTRING(254)	String in which characters should be replaced String 2 with all characters to be  replaced String 3 with characters to  replace with characters at the same place in string 2
Output	CSTRING(32760)	String in which all characters where  replaced with characters from string 3
TestSQL            SELECT '(a+ab)-abc*a+b=c' AS ISCORRECT, F_CONVERTSYMBOLS('(1+12)-123*1+2=3', '123', 'abc') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_CONVERTSYMBOLS(NULL, NULL, NULL) FROM RDB$DATABASE;
from		substitutable with REVERSE
F_REVERSE		function from adhoc
Entrypoint	reverse	not compatible with UTF-8 - use U_REVERSE
U_REVERSE		function from adhoc
Entrypoint	u_reverse	compatible with UTF-8
Input	CSTRING(254)	string to return backwards
Output	CSTRING(254)	string backwards
Upper-/lower-case stays as in the original string. TestSQL SELECT 'ynamreG' AS ISCORRECT, F_REVERSE('Germany') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_REVERSE(NULL) FROM RDB$DATABASE;
F_STRIPSTRING F_BIGSTRIPSTRING		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	stripstring	not compatible with UTF-8 - use U_STRIPSTRING
F_STRIPSTRING		function from adhoc
Entrypoint	u_stripstring	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254)	String 1 String 2  as a list of all characters which are to delete
Output	CSTRING(32760)	String stripped from all characters which are content of parameter 2
Counterpart to F_STRIPSTRINGHOLD and F_HOLDSTRING TestSQL SELECT 'Ds s n Ts Tx' AS ISCORRECT, F_STRIPSTRING('Dies ist ein Test Text', 'iet') FROM RDB$DATABASE; SELECT 'Ds s n Ts Tx' AS ISCORRECT, F_STRIPSTRING('Dies ist ein Test Text', 'tei') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRIPSTRING(NULL, NULL) FROM RDB$DATABASE;
F_STRIPSTRINGHOLD F_HOLDSTRING F_BIGSTRIPSTRINGHOLD		compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF compatibility to FreeUDFLibC
Entrypoint	stripstringhold	not compatible with UTF-8 - use U_STRIPSTRINGHOLD
U_STRIPSTRINGHOLD		function from adhoc
Entrypoint	u_stripstringhold	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254)	String 1 String 2 as a list of all characters which are not to delete
Output	CSTRING(32760)	String stripped from all characters which are not content of parameter 2
The sequence of the indications in the string 2 does not matter. Counterpart to F_STRIPSTRING Note: In a SQL F_HOLDSTRING and F_STRIPSTRINGHOLD may not use at the same time. TestSQL SELECT 'ieiteietet' AS ISCORRECT, F_HOLDSTRING('Dies ist ein Test Text', 'iet') FROM RDB$DATABASE; SELECT 'ieiteietet' AS ISCORRECT, F_HOLDSTRING('Dies ist ein Test Text', 'tei') FROM RDB$DATABASE; SELECT 'ieiteietet' AS ISCORRECT, F_HOLDSTRING('Dies ist ein Test Text', 'iet') FROM RDB$DATABASE; SELECT 'ieiteietet' AS ISCORRECT, F_HOLDSTRING('Dies ist ein Test Text', 'tei') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRIPSTRINGHOLD(NULL, NULL) FROM RDB$DATABASE;
F_REPEATTRIM		input/output-compatibility to rFunc (REPEATTRIM, LONGREPEATTRIM)
Entrypoint	repeattrim	not compatible with UTF-8 - use U_REPEATTRIM
U_REPEATTRIM
Entrypoint	u_repeattrim	compatible with UTF-8
Input	CSTRING(8190) CSTRING(1)	String 1 from which characters will be removed character to remove
Output	CSTRING(8190)	String with removed all repeatings of characters parameter 2
TestSQL SELECT '123' AS ISCORRECT, F_REPEATTRIM('12223', '2') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_REPEATTRIM(NULL, NULL) FROM RDB$DATABASE;
F_STRREPEAT		input/output-compatibility to rFunc (STRREPEAT, LONGSTRREPEAT)
Entrypoint	strrepeat	compatible with UTF-8
Input	CSTRING(254) SMALLINT	String to repeat count of  appearance
Output	CSTRING(8190)	String 1 with parameter 2 times appearances
You have to look that the output is not greater than the declaration! TestSQL SELECT 'TestTestTest' AS ISCORRECT, F_STRREPEAT('Test', 3) FROM RDB$DATABASE; SELECT '' AS ISCORRECT, F_STRREPEAT('Test', 0) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRREPEAT(NULL, NULL) FROM RDB$DATABASE;
F_STROFCHAR		compatibility to GrUDF
Entrypoint	strofchar	not compatible with UTF-8 - use F_STRREPEAT
Input	CSTRING(1) INTEGER	String to repeat Number of repetitions
Output	CSTRING(32760)	Returns a string with the indicated number of repeated indications
TestSQL SELECT F_STRINGLENGTH(F_STROFCHAR('A', 10)) || ' mal A' AS ISCORRECT, F_STROFCHAR('A', 10) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STROFCHAR(NULL, NULL) FROM RDB$DATABASE;
from		substitutable with OVERLAY
F_STRSTUFF		input/output-compatibility to rFunc (STRSTUFF, LONGSTRSTUFF)
Entrypoint	strstuff	not compatible with UTF-8 - use U_STRSTUFF
U_STRSTUFF		function from adhoc
Entrypoint	u_strstuff	compatible with UTF-8
Input	CSTRING(8190) SMALLINT SMALLINT CSTRING(254)	String 1 in which characters had to replace starting-position number of characters to delete characters to set
Output	CSTRING(81900)	String with replaced characters at starting-postion
Counting oof starting-position starts at 1. The numbers of characters to delete and characters to replace must not be equal. TestSQL SELECT '12abcd567890' AS ISCORRECT, F_STRSTUFF('1234567890', 3, 2, 'abcd') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRSTUFF(NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
F_COLLATEBR		compatibility to  GrUDF
Entrypoint	collatebr	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760)	String in which special characters are to be converted
Output	CSTRING(32760)	changed String
Changes intended "umlauts" for given indications á, â, ã, à, ä, å, Á, Â, Ã, À, Ä, Å      => A é, ê, è, ë, É, Ê, È, Ë                         => E í, î, ì, ï, Í, Î, Ì, Ï                                 => I ó, ô, õ, ò, ö, Ó, Ô, Õ, Ò, Ö            => O ú, û, ù, ü,Ú, Û, Ù, Ü                        => U ç, Ç                                                => C ñ, Ñ                                                => N ý, ÿ, Ý                                            => Y Note: TestSQL SELECT 'AAAAAAAAAAAA' AS ISCORRECT, F_COLLATEBR('áâãàäåÁÂÃÀÄÅ') FROM RDB$DATABASE; SELECT 'EEEEEEEE' AS ISCORRECT, F_COLLATEBR('éêèëÉÊÈË') FROM RDB$DATABASE; SELECT 'IIIIIIII' AS ISCORRECT, F_COLLATEBR('íîìïÍÎÌÏ') FROM RDB$DATABASE; SELECT 'OOOOOOOOOO' AS ISCORRECT, F_COLLATEBR('óôõòöÓÔÕÒÖ') FROM RDB$DATABASE; SELECT 'UUUUUUUU' AS ISCORRECT, F_COLLATEBR('úûùüÚÛÙÜ') FROM RDB$DATABASE; SELECT 'CC' AS ISCORRECT, F_COLLATEBR('çÇ') FROM RDB$DATABASE; SELECT 'NN' AS ISCORRECT, F_COLLATEBR('ñÑ') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_COLLATEBR(NULL) FROM RDB$DATABASE;
F_KEYUP		function from adhoc
Entrypoint	keyup	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760) SMALLINT	String to prepaired length of output-string
Output	CSTRING(32760)	prepaired string
Prepaires a string with - all to upper - uses only alphanumeric characters incl. umlaut - changes intended "umlauts" for given indications      Á, Â, Ã, À, Ä, Å      => A      É, Ê, È, Ë                 => E      Í, Î, Ì, Ï                     => I      Ó, Ô, Õ, Ò, Ö         => O      Ú, Û, Ù, Ü               => U      Ç                             => C      Æ                            => AE      Ø                            => OE      ß                             => SS      Đ                            => D      Ñ                            => N      Ý                            => Y - truncate string to given length TestSQL SELECT '7A 5E 5I 6O 5U 2C 2AE 2OE 4S 2D 2N 2Y 1234567' AS ISCORRECT, F_KEYUP('AáâãàäåEéêèëIíîìïOóôõòöUúûùüCçAEæOEøSSßDd.NñYý1234567890', 55) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_KEYUP(NULL, NULL) FROM RDB$DATABASE;
F_PRED		function from adhoc
Entrypoint	predecessor	not compatible with UTF-8 - use U_PRED
U_PRED		function from adhoc
Entrypoint	u_predecessor	compatible with UTF-8
Input	CSTRING(1) INTEGER	Character Amount of characters before the character in ASCII-Table
Output	CSTRING(1)	n-predecessor of character from parameter 1
Is parameter 2 equal to 512 (or multiple of 512) the return is the same as the input. Test SQL SELECT 'b' AS ISCORRECT, F_PRED('a', -1) FROM RDB$DATABASE; SELECT 'a' AS ISCORRECT, F_PRED('b', 1) FROM RDB$DATABASE; SELECT 'a' AS ISCORRECT, F_PRED('a', 512) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_PRED(NULL, NULL) FROM RDB$DATABASE; SELECT '¢' AS ISCORRECT, U_PRED('£', 1) FROM RDB$DATABASE; SELECT '¡' AS ISCORRECT, U_PRED('£', 2) FROM RDB$DATABASE; SELECT 'あ' AS ISCORRECT, U_PRED('え', 6) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, U_PRED(NULL, NULL) FROM RDB$DATABASE;
F_SUCC		function from adhoc
Entrypoint	successor	not compatible with UTF-8 - use U_SUCC
U_SUCC		function from adhoc
Entrypoint	u_successor	compatible with UTF-8
Input	CSTRING(1) INTEGER	Character Amount of characters behind the character in ASCII-Table
Output	CSTRING(1)	n-successor of character from parameter 1
Is parameter 2 equal to 512 (or multiple of 512) the return is the same as the input. Test SQL SELECT 'a' AS ISCORRECT, F_SUCC('b', -1) FROM RDB$DATABASE; SELECT 'c' AS ISCORRECT, F_SUCC('b', 1) FROM RDB$DATABASE; SELECT 'b' AS ISCORRECT, F_SUCC('b', 512) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_SUCC(NULL, NULL) FROM RDB$DATABASE; SELECT '¤' AS ISCORRECT, U_SUCC('£', 1) FROM RDB$DATABASE; SELECT '×' AS ISCORRECT, U_SUCC('Ö', 1) FROM RDB$DATABASE; SELECT 'Γ' AS ISCORRECT, U_SUCC('Ώ', 4) FROM RDB$DATABASE; SELECT 'ぎ' AS ISCORRECT, U_SUCC('え', 6) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, U_SUCC(NULL, NULL) FROM RDB$DATABASE;

 

String-functions: Convert
from		substitutable with LOWER
F_LOWER F_ANSILOWERCASE		function from adhoc compatibility to  FreeUDFLib AvERP, GrUDF
Entrypoint	lower	not compatible with UTF-8 - use U_LOWER
U_LOWER		function from adhoc
Entrypoint	u_lower	compatible with UTF-8
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all characters as lower incl. umlaut
In FireBird 2.0 no longer necessary. Use LOWER(..) All umlaut are handled correct. TestSQL SELECT 'schöner tag' AS ISCORRECT, F_LOWER('SchÖner TAG') FROM RDB$DATABASE; SELECT 'schöner tag' AS ISCORRECT, F_ANSILOWERCASE('SchÖner TAG') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT,  F_LOWER(NULL) FROM RDB$DATABASE;
from		substitutable with UPPER
F_UPPER F_ANSIUPPERCASE		function from adhoc compatibility to FreeUDFLib AvERP, GrUDF
Entrypoint	upper	not compatible with UTF-8 - use U_UPPER
U_UPPER		function from adhoc
Entrypoint	u_upper	compatible with UTF-8
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all characters as upper incl. umlaut
In FireBird 2.0 no longer necessary. Use UPPER(..) All umlaut are handled correct. TestSQL SELECT 'SCHÖNER TAG' AS ISCORRECT, UPPER('Schöner Tag'), F_UPPER('Schöner Tag') FROM RDB$DATABASE; SELECT 'SCHÖNER TAG' AS ISCORRECT, UPPER('Schöner Tag'), F_ANSIUPPERCASE('Schöner Tag') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, UPPER(NULL), F_UPPER(NULL) FROM RDB$DATABASE;
from		substitutable with LOWER
F_RLOWER		compatibility to rFunc (RLOWER, LONGRLOWER) only for charset Win1251
Entrypoint	rlower	not compatible with UTF-8 - use U_LOWER
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all characters as lower
Converts cyrillic upper to cyrillic lower
from		substitutable with UPPER
F_RUPPER		compatibility to rFunc (RUPPER, LONGRUPPER) only for charset Win1251
Entrypoint	rupper	not compatible with UTF-8 - use U_UPPER
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all characters as upper
Converts cyrillic lower to cyrillic upper
Preliminary note (look http://en.wikipedia.org/wiki/Cyrillic#Romanization) There are various systems for romanization of Cyrillic text, including transliteration to convey Cyrillic spelling in Latin characters, and transcription to convey pronunciation. Standard Cyrillic-to-Latin transliteration systems include:     * Scientific transliteration, used in linguistics, is based on the Latin Croatian alphabet.     * The Working Group on Romanization Systems of the United Nations recommends different systems for specific languages.        These are the most commonly used around the world.     * ISO 9:1995, from the International Organization for Standardization.     * American Library Association and Library of Congress Romanization tables for Slavic alphabets (ALA-LC Romanization),        used in North American libraries.     * BGN/PCGN romanization (1947), United States Board on Geographic Names & Permanent Committee on Geographical        Names for British Official Use).     * GOST 16876, a now defunct Soviet transliteration standard. Replaced by GOST 7.79, which is ISO 9 equivalent.     * Volapuk encoding, an informal rendering of Cyrillic text over Latin-alphabet ASCII. Characters for use with scientific transliteration: cyrillic А а Б б В в Ѓ ѓ Д д Ђ ђ Е е Ё ё Ж ж З з И и Й й К к Л л М м Н н О о П п Р р С с Т т Ќ ќ Ћ ћ У у Ў ў Ф ф Ц ц Ч ч Ш ш Ь ь Ѣ ѣ latin A a B b V v Ǵ ǵ D d Đ đ E e Ë ë Ž ž Z z I i J j K k L l M m N n O o P p R r S s T t Ḱ ḱ Ć ć U u Ǔ ŭ F f C c Č č Š š ′ Ě ě For scientific use the scientific transliteration is favored, also in reference books and on maps, for media the transscription is preffered.
F_RLATIN		compatibility to rFunc (RLATIN, LONGLATIN) only for charset Win1251
Entrypoint	rlatin	not compatible with UTF-8 - use U_CYRILLIC_LATIN
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all characters as latin
Converts cyrillic characters to latin characters with method scientific transliteration. Remark: For compatibilty reasons to rFunc converting was hold for F_RLATIN with the following convertings: cyrillic Ё ё Є є Ї ї І і latin E e E e I i I i cyrillic А а Б б В в Ѓ ѓ Д д Ђ ђ Е е Ё ё Ж ж З з И и Й й К к Л л М м Н н О о П п Р р С с Т т Ќ ќ Ћ ћ У у Ў ў Ф ф Ц ц Ч ч Ш ш Ь ь Ѣ ѣ latin A a B b V v Ǵ ǵ D d Đ đ E e Ë ë Ž ž Z z I i J j K k L l M m N n O o P p R r S s T t Ḱ ḱ Ć ć U u Ǔ ŭ F f C c Č č Š š _ Ě ě Converting is ONLY successful if database runs charset win1251.
U_CYRILLIC_LATIN		function from adhoc
Entrypoint	u_cyrillic_latin	compatible with UTF-8
Input	CSTRING(8191)	String to convert
Output	CSTRING(8191)	String with all characters as latin
Converts cyrillic characters to latin characters with method scientific transliteration from ISO 9. Usable for Russian, Bulgarian, Serbo-Croatian, Ukrainian and Belorussian. Transliteration only for UTF-8 databases and Inputs. TestSQL SELECT 'Aleksandr Solženicyn' AS ISCORRECT, U_CYRILLIC_LATIN('Александр Солженицын') FROM RDB$DATABASE SELECT 'Mihail Gorbačëv' AS ISCORRECT, U_CYRILLIC_LATIN('Михаил Горбачёв') FROM RDB$DATABASE SELECT 'Ruslana Ližičko' AS ISCORRECT, U_CYRILLIC_LATIN('Руслана Лижичко') FROM RDB$DATABASE SELECT 'AaBbVvGgDdEeË뎞ZzIiJjKkLlMmNnOoPpRrSsTtUuFfHhCcČ芚ŜŝʺʺYyʹʹÈèÛûÂâ' AS ISCORRECT, U_CYRILLIC_LATIN('АаБбВвГгДдЕеЁёЖжЗзИиЙйКкЛлМмНнОоПпРрСсТтУуФфХхЦцЧчШ шЩщЪъЫыЬьЭэЮюЯя') FROM RDB$DATABASE SELECT NULL AS ISCORRECT, F_CYRILLLIC_LATIN(NULL) FROM RDB$DATABASE;
F_PROPERCASE		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	propercase	not compatible with UTF-8 - use U_PROPERCASE
U_PROPERCASE		function from adhoc
Entrypoint	u_propercase	compatible with UTF-8
Input	CSTRING(32760)	String to convert
Output	CSTRING(32760)	String with all words starting with upper, all other lower
Changes also umlaut. A lower "ß" stays "ß" because there is no upper "ß" TestSQL SELECT 'Dies Ist Ein Test Äh' AS ISCORRECT, F_PROPERCASE('dies ist ein test äh') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_PROPERCASE(NULL) FROM RDB$DATABASE;
from		substitutable with ASCII_CHAR
F_CHARACTER F_CHR		compatibility to  FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP compatibility to  GrUDF input/output-compatibility to rFunc (CHR)
Entrypoint	character	not compatible with UTF-8 - use U_CHR
U_CHR		function from adhoc
Entrypoint	u_character	compatible with UTF-8
Input	INTEGER	ASCII-Code
Output	CSTRING(2)	Character of ASCII-Codes
TestSQL SELECT 'B' AS ISCORRECT, F_CHARACTER(66) FROM RDB$DATABASE; SELECT 'ä' AS ISCORRECT, F_CHARACTER(228) FROM RDB$DATABASE; SELECT 'B' AS ISCORRECT, F_CHR(66) FROM RDB$DATABASE; SELECT 'ä' AS ISCORRECT, F_CHR(228) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_CHARACTER(NULL) FROM RDB$DATABASE;
from		substitutable with ASCII_VAL
F_ORD		input/output-compatibility to rFunc (ORD)
Entrypoint	ord	not compatible with UTF-8 - use U_ORD
F_ORD		function from adhoc
Entrypoint	u_ord	compatible with UTF-8
Input	CSTRING(1)	ASCII-Code-No.
Output	SMALLINT	Character of ASCII-Code-No.
TestSQL SELECT 65 AS ISCORRECT, F_ORD('A') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ORD(NULL) FROM RDB$DATABASE;
F_ROT13		function from adhoc
Entrypoint	rot13	compatible with UTF-8
Input	CSTRING(254)	String to encrypt
Output	CSTRING(254)	String enrypted with ROT13
From Wikipedia: ROT13 ("rotate by 13 places", sometimes hyphenated ROT-13) is a simple substitution cipher used in online forums as a means of hiding spoilers, punchlines, puzzle solutions, and offensive materials from the casual glance. ROT13 has been described as the "Usenet equivalent of a magazine printing the answer to a quiz upside down". ROT13 is a variation of the Caesar cipher, developed in ancient Rome. Applying ROT13 to a piece of text merely requires examining its alphabetic characters and replacing each one by the letter 13 places further along in the alphabet, wrapping back to the beginning if necessary. ROT13 is its own inverse; that is, to undo ROT13, the same algorithm is applied, so the same action can be used for encoding and decoding. The algorithm provides no real cryptographic security and is not normally used for such. It is often cited as a canonical example of weak encryption. TestSQL SELECT F_ROT13(F_ROT13('This is not a real encryption')), F_ROT13('This is not a real encryption') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ROT13(NULL) FROM RDB$DATABASE;
F_GENERATESNDXINDEX F_SOUNDEX		compatibility to  FreeUDFLib, FreeUDFLib AvERP function from adhoc
Entrypoint	soundex	compatible with UTF-8
Input	CSTRING(8190)	String to generate Soundex
Output	CSTRING(6)	original (american) soundex of string
Soundex is a phonetic algorithm to indexation of words and cliches after its sound in the English language.  Equalequivalent words are to be coded thereby to an identical character sequence.  Considered starting from the 2.  place counted only the first 6 consonants with the determination, whereby repeated occurrence is only once considered (in the example the l,m,y,w,r,d).  Therefore it is unsuitable, around longer strings to compare. TestSQL SELECT 'H4564' AS ISCORRECT, F_GENERATESNDXINDEX('Hello my world') FROM RDB$DATABASE; SELECT 'H4564' AS ISCORRECT, F_GENERATESNDXINDEX('Hello my world on my earth') FROM RDB$DATABASE; SELECT 'H4564' AS ISCORRECT, F_SOUNDEX('Hello my world') FROM RDB$DATABASE; SELECT 'H4564' AS ISCORRECT, F_SOUNDEX('Hello my world on my earth') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_SOUNDEX(NULL) FROM RDB$DATABASE;
F_GSOUNDEX		function from adhoc
Entrypoint	gsoundex	not compatible with UTF-8 - if needed please ask
Input	CSTRING(8190)	String
Output	CSTRING(8190)	(german) soundex of string
Determines the german-phonetic string of parameter 1. Could be used e.g. for the doublet search. TestSQL SELECT 'MAYR' AS ISCORRECT, F_GSOUNDEX('Meier'), F_GSOUNDEX('Maier'), F_GSOUNDEX('Mayer') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_GSOUNDEX(NULL), F_GSOUNDEX(NULL), F_GSOUNDEX(NULL) FROM RDB$DATABASE;
F_TELEFONNR		function from adhoc
Entrypoint	telefonnr	compatible with UTF-8
Input	CSTRING(32760)	Telephone no. as string (or something similar, from which everything is to be removed up to the numbers)
	INTEGER	defines the amount of numbers at the end to replace with *
Output	CSTRING(32760)	String without all non numbers
"49" at the beginning is changed to "+49". If the string starts with one "+", this remains.  Note: This function is necessary for "wildly" formatted telephonenumbers for TAPI to a Telephone(system) which couldn't do this by itsself. TestSQL SELECT '0232653***' AS ISCORRECT, F_TELEFONNR(' (0232) / 6535-35', 3) FROM RDB$DATABASE; SELECT '+001232653***' AS ISCORRECT, F_TELEFONNR('+001 (232) / 6535-35', 3) FROM RDB$DATABASE; SELECT '+49232653***' AS ISCORRECT, F_TELEFONNR('+49 (232) / 6535-35', 3) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_TELEFONNR(NULL, NULL) FROM RDB$DATABASE;
F_DIGITS		compatibility to   GrUDF
Entrypoint	digits	compatible with UTF-8
Input	CSTRING(32760)	String from which all non-numbers had to be remove
Output	CSTRING(32760)	String with removed all non-numbers (e.g. for TAPI)
TestSQL SELECT '0232653535' AS ISCORRECT, F_DIGITS(' (0232) / 6535-35') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_DIGITS(NULL) FROM RDB$DATABASE;
F_STR2EXCEL		function from adhoc
Entrypoint	excelstring	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760)	String
Output	CSTRING(32760)	String changed for Excel
To convert multiline texts and texts with converted commas to Excel, it is required to transform the string. This function will do the following: -    appends a double inverted comma at the beginning and the end of the string -    doubles all inverted commas -    deletes all CHR(13) in the string -    limits the input string to 32760 characters (Limitation in Excel) TestSQL SELECT '"1.Zeile ""Paul"" und' || F_LF() || '2.Zeile"' AS ISCORRECT, F_STR2EXCEL('1.Zeile "Paul" und' || F_CRLF() || '2.Zeile') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STR2EXCEL(NULL) FROM RDB$DATABASE; Note: Actually it doesn’t make any sense to export a very large text from the database to excel cell, this function may be practicable in combinaton with F_LEFT or F_RIGHT. For instance: SELECT F_RIGHT(F_STR2EXCEL(STRFeldTAGEBUCH), 1000) FROM ... exportiert z.B. die letzten 1000 Zeichen des Tagebuchs.
F_RC		input/output-compatibility to rFunc ( C, LONGC)
Entrypoint	r_c	compatible with UTF-8
Input	CSTRING(254)	String
Output	CSTRING(254)	is input-string <null> returns an empty string, either the input-string
TestSQL SELECT 'Leerstring/empty string' AS ISCORRECT, F_RC(UUIDCOMPR) FROM UUIDTEST WHERE UUIDTESTID = 5;
F_FLOATOSTR		input/output-compatibility to rFunc (FLOATTOSTR)
Entrypoint	floattostr	compatible with UTF-8
Input	DOUBLE CSTRING(254)	floating-point number to display format of floatoing-point equal to C-function sprintf
Output	CSTRING(254)	formated number as string
TestSQL SELECT '1.350e+01' AS ISCORRECT, F_FLOATTOSTR(13.5, '%7.3e') FROM RDB$DATABASE; SELECT '+13.500' AS ISCORRECT, F_FLOATTOSTR(13.5, '%+7.3f') FROM RDB$DATABASE; SELECT '13.534' AS ISCORRECT, F_FLOATTOSTR(13.5345, '%.5g') FROM RDB$DATABASE; SELECT '13.53450' AS ISCORRECT, F_FLOATTOSTR(13.5345, '%.5f') FROM RDB$DATABASE; SELECT '0000D' AS ISCORRECT, F_FLOATTOSTR(13, '%05X') FROM RDB$DATABASE; SELECT '0x0d' AS ISCORRECT, F_FLOATTOSTR(13, '%#04x') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FLOATTOSTR(NULL, NULL) FROM RDB$DATABASE;
F_INTTOSTR		input/output-compatibility to rFunc (INTTOSTR)
Entrypoint	inttostr	compatible with UTF-8
Input	INTEGER CSTRING(254)	integer number to display format of integer equal to C-function sprintf
Output	CSTRING(254)	formated number as string
TestSQL SELECT '0000000013' AS ISCORRECT, F_INTTOSTR(13, '%010d') FROM RDB$DATABASE; SELECT '0xd' AS ISCORRECT, F_INTTOSTR(13, '%#x') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_INTTOSTR(NULL, NULL) FROM RDB$DATABASE;

 

String-functions: Compare
F_EQUALSTRING		compatibility to FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (CEQUAL)
Entrypoint	equalstring	compatible with UTF-8
Input	CSTRING(8190) CSTRING(8190)	String 1 String 2
Output	INTEGER	1 = is equal, 0 = is not equal
TestSQL SELECT 1 AS ISCORRECT, F_EQUALSTRING('Pauline ist im Urlaub', 'Pauline ist im Urlaub') FROM RDB$DATABASE; SELECT 0 AS ISCORRECT, F_EQUALSTRING('Pauline ist im Urlaub', 'Paul ist im Urlaub') FROM RDB$DATABASE; In FireBird 2.0 WITH <null>-declaration the following example returns <null>, in all other databases the NULL-string is identical to an empty string, therefore the following example returns 1. SELECT NULL AS ISCORRECT, F_EQUALSTRING(NULL, '') FROM RDB$DATABASE;
F_STRCMP		input/output-compatibility to rFunc (STRCMP)
Entrypoint	r_strcmp	not compatible with UTF-8 - use U_STRCMP
U_STRCMP		function from adhoc
Entrypoint	u_strcmp	compatible with UTF-8
Input	CSTRING(8190) CSTRING(8190)	String 1 String 2
Output	INTEGER	output < 0 : string 1 smaller than string 2 output = 0 : string 1 equal to string 2 output > 0 : string 1 greater than string 2
Attention: in original rFunc-functions STRCMP the output positiv or negativ is different to the rFunc-BLOb-function B_STRCMP - in string-function a positiv output meens, that the first string is greater, in BLOb-function it meens, that the second BLOb is greater. For compatibility reasons the FreeAdhocUDF do exactly the same. In Linux the value of output is other than in Windows! TestSQL SELECT 0 AS ISCORRECT, F_STRCMP('Pauline ist im Urlaub', 'Pauline ist im Urlaub') FROM RDB$DATABASE; SELECT 73 AS ISCORRECT, F_STRCMP('Pauline ist im Urlaub', 'Paul ist im Urlaub') FROM RDB$DATABASE; SELECT -73 AS ISCORRECT, F_STRCMP('Paul ist im Urlaub', 'Pauline ist im Urlaub') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRCMP(NULL, NULL) FROM RDB$DATABASE;

 

String-functions: Find / Determin
F_FINDWORD		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
Entrypoint	findword	not compatible with UTF-8 - use U_FINDWORD
U_FINDWORD		function from adhoc
Entrypoint	u_findword	compatible with UTF-8
Input	CSTRING(32760) INTEGER	String, in which another string is to be looked for Position, at which the search in the string should starts
Output	CSTRING(254)	word or a partial word , which starts at positon parameter 2
Positioning starts with 0, that means 1.position = 0 and searches up to the first blank. In FireBird 2.0 returns <null> if parameter 2 < 0 or > length of parameter 1, all other cases '' (empty string). TestSQL SELECT 'ABC' AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 0) FROM RDB$DATABASE; SELECT 'BC' AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 1) FROM RDB$DATABASE; SELECT '123' AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 3) FROM RDB$DATABASE; SELECT '123' AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 4) FROM RDB$DATABASE; SELECT '' AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 20) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FINDWORD('ABC 123 45678 9123', 20) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FINDWORD(NULL, NULL) FROM RDB$DATABASE;
F_WORDNUM		input/output-compatibility to rFunc (WORDNUM, LONGWORDNUM)
Entrypoint	wordnum	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760) INTEGER CSTRING(31) SMALLINT	string, in which an other string is looked for no. of string to find (n-ths word) word delimiter
Output	CSTRING(254)	nth-word
Countinf for parameter 2 starts with 1. TestSQL SELECT 'ABC' AS ISORRECT, F_WORDNUM('ABC 123 45678 9123', 1, ' ', 0) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_WORDNUM(NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
F_FINDNTHWORD		compatibility to FreeUDFLib, FreeUDFLib AvERP
Entrypoint	findnthword	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760) INTEGER	String, in which another string is to be looked for Word-number, which to be searched (nth word)
Output	CSTRING(254)	n-word
Counting starts at 0. In FireBird 2.0 returns <null> if there are not so many words than indicated in parameter 2, all other cases '' (empty string). TestSQL SELECT 'ABC' AS ISCORRECT, F_FINDNTHWORD('ABC 123 45678 9123', 0) FROM RDB$DATABASE; SELECT '123' AS ISCORRECT, F_FINDNTHWORD('ABC 123 45678 9123', 1) FROM RDB$DATABASE; SELECT '' AS ISCORRECT, F_FINDNTHWORD('ABC 123 45678 9123', 5) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FINDNTHWORD(NULL, NULL) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FINDNTHWORD('ABC 123 45678 9123', 5) FROM RDB$DATABASE;
F_FINDWORDINDEX		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
Entrypoint	findwordindex	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760) INTEGER	String length of the string which should be examined
Output	INTEGER	examines whether the position from Paramter 2 exists in the string. If the poition is found in the string, the result is the position itself, if not found the result is -1.
Counting starts at 0. TestSQL SELECT 12 AS ISCORRECT, F_FINDWORDINDEX('Geburtstagsparty', 12) FROM RDB$DATABASE; SELECT -1 AS ISCORRECT, F_FINDWORDINDEX('Geburtstagsparty', 16) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FINDWORDINDEX(NULL, NULL) FROM RDB$DATABASE;
F_FINDWORDINDEXN (F_FINDWORDINDEX)		function from adhoc
Entrypoint	findwordindexnull	not compatible with UTF-8 - if needed please ask
Input	CSTRING(32760) INTEGER	String length of the string which should be examined
Output	INTEGER	examines whether the position from Paramter 2 exists in the string. If the poition is found in the string, the result is the position itself, if not found the result is <null>
Counting starts at 0. FireBird 2.0 variant for F_FINDWORDINDEX. TestSQL SELECT  NULL AS ISCORRECT, F_FINDWORDINDEXN('Geburtstagsparty', 16) FROM RDB$DATABASE;
from		substitutable with CHARACTER_LENGTH or CHAR_LENGTH
F_STRINGLENGTH (..4, ..8, ..32) F_BIGSTRINGLENGTH		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (STRLEN)
Entrypoint	stringlength	not compatible with UTF-8 - use U_STRINGLENGTH
F_STRINGLENGTH (..4, ..8, ..32)		function from adhoc
Entrypoint	u_stringlength	compatible with UTF-8
Input	CSTRING(32760)	String
Output	INTEGER	Determines the length of a string, considers also simple blanks at the end
F_STRINGLENGTH and F_BIGSTRINGLENGTH are not allowed in one SQL. TestSQL SELECT 17 AS ISCORRECT, F_STRINGLENGTH('Dies ist ein Test') FROM RDB$DATABASE; SELECT 19 AS ISCORRECT, F_STRINGLENGTH('Dies ist ein Test  ') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRINGLENGTH(NULL) FROM RDB$DATABASE;
F_STRINGLISTITEM		compatibility to GrUDF
Entrypoint	stringlistitem	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254)	stringlist formatted like ”n=..”,”m=..”, a.s.o. Value from the list to be shown
Output	CSTRING(1024)	Searchs the string, build a stringlist (Name=Value) for the Name and returns the complete Name=Value
TestSQL SELECT '2=gelb' AS ISCORRECT, F_STRINGLISTITEM('"1=blau","2=gelb","3=grün","4=rot"', '2') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_STRINGLISTITEM(NULL, NULL) FROM RDB$DATABASE;
F_WORDCOUNT		function from adhoc
Entrypoint	wordcount	compatible with UTF-8
Input	CSTRING(32760)	String in which the amount of words are to be calculated
Output	INTEGER	amount of words in the string
A word ends with one ore more spaces, TAB or CRLF. TestSQL SELECT 4 AS ISCORRECT, F_WORDCOUNT('ABC 123 45678 9123') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_WORDCOUNT(NULL) FROM RDB$DATABASE;
F_RWORDCOUNT		input/output-compatibility to rFunc (WORDCOUNT)
Entrypoint	r_wordcount	not compatible with UTF-8 - use U_WORDCOUNT
Input	CSTRING(32760) CSTRING(31) SMALLINT	String word delimiter
Output	INTEGER	number of words
TestSQL SELECT 4 AS ISCORRECT, F_RWORDCOUNT('ABC 123 45678 9123', ' ', 0) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_RWORDCOUNT(NULL, NULL, NULL) FROM RDB$DATABASE;
F_STRINGCOUNT		function from adhoc
Entrypoint	stringcount	compatible with UTF-8
Input	CSTRING(32760) CSTRING(254)	String1 String2
Output	INTEGER	Amount of String 2 in String 1
F_RSTRINGCOUNT		input/output-compatibility to rFunc (STRINGCOUNT)
Entrypoint	r_stringcount	compatible with UTF-8
Input	CSTRING(254) CSTRING(32760)	String 1 String 2
Output	INTEGER	Amount of String 1 in String 2
TestSQL SELECT 2 AS ISCORRECT, F_STRINGCOUNT( 'ABC 123 45678 9123', '123'), F_RSTRINGCOUNT('123', 'ABC 123 45678 9123') FROM RDB$DATABASE; SELECT 0 AS ISCORRECT, F_STRINGCOUNT( 'ABC 123 45678 9123', 'EFG'), F_RSTRINGCOUNT('EFG', 'ABC 123 45678 9123') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_RSTRINGCOUNT(NULL, NULL) FROM RDB$DATABASE;
F_DOMAINFROMEMAIL		function from adhoc
Entrypoint	domainfromemail	compatible with UTF-8
Input	CSTRING(254)	eMail-String
Output	CSTRING(254)	from eMail substituted domain
TestSQL SELECT 'adhoc-data.de' AS ISCORRECT, F_DOMAINFROMEMAIL('udf@adhoc-data.de') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_DOMAINFROMEMAIL(NULL) FROM RDB$DATABASE;