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 •Forum mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
 
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
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
nach oben • go top •
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
nach oben • go top •
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.
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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.
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site