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 •Umfrage • poll • sondage mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
BLOb functions 21 functions
Transform 3 functions
Edit 9 functions
Calculate 5 functions
Compare 2 functions
Find / Determin 2 functions
function can't (yet) return <null> in FireBird 2.0
since InterBase 2007 this function is substitutable with a native SQL statem.
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.0
 
BLOb-functions: Transform
F_BLOBASPCHAR
compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
obsolete, BLObs could be managed like strings
Entrypoint blobaspchar
Input BLOB
TextBlob to convert to VarChar
Output CSTRING(32760) String
TestSQL (to use with TestISO.GDB)
SELECT 'ein einzeiliger TextBLOb' AS ISCORRECT, TEXTBLOB, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 1;
SELECT '' AS ISCORRECT, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 11;
SELECT NULL AS ISCORRECT, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_STRBLOB  compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (B_PUT_SEGMENT)
obsolete, BLObs could be managed like strings
Entrypoint strblob
Input CSTRING(32760) String to convert to BLOb
Output BLOB BLOb
TestSQL
SELECT 'soviel für heute' AS ISCORRECT, F_BLOBASPCHAR(F_STRBLOB('soviel für heute')) FROM RDB$DATABASE;
nach oben • go top •
F_BLOB2EXCEL function from adhoc
obsolete, BLObs could be managed like strings
Entrypoint excelblob
Input BLOB BLOb to convert to Excel
Output CSTRING(32760) String to use with Excel
To convert multiline texts and texts with converted commas to Excel, it is required to transform the blob. This function will do the following:
-    appends a double inverted comma at the beginning and the end of the blob
-    doubles all inverted commas in the blob
-    deletes all CHR(13) in the blob
-    limits the input blob to 32760 characters (limitation in Excel)
TestSQL
SELECT '"ein dreizeiliger TextBLOb' || F_LF() || 'mit einer zweiten Zeile' || F_LF() || 'und einer dritten Zeile"' AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
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 combinatin with F_LEFT or F_RIGHT for instance:   
SELECT F_RIGHT(F_BLOB2EXCEL(BLObFeldTAGEBUCH), 1000) FROM ... exportiert z.B. die letzten 1000 Zeichen des Tagebuchs.
nach oben • go top •
 
BLOb-functions: Edit
F_BLOBLEFT
compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
substitutable with F_LEFT
substitutable with LEFT
Entrypoint blobleft
Input BLOB
INTEGER
TextBLOb
Length of returned string
Output CSTRING(32760) string which is cut by the indicationnumber from left gives from parameter 2
Counting starts with 1
TestSQL (to use with TestISO.GDB)
SELECT 'ein einzeiliger' AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, 15) FROM BLOBTEST WHERE BLOBTESTID = 1;
SELECT '' AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, 15) FROM BLOBTEST WHERE BLOBTESTID = 11;
SELECT NULL AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_BLOBMID
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
substitutable with F_MID
Entrypoint blobmid
F_RBLOBMID input/output-compatibility to rFunc (B_SUBSTR, B_LONGSUBSTR)
substitutable with F_COPY or F_STRCOPY
Entrypoint r_blobmid
Input BLOB
INTEGER
INTEGER
TextBLOb
Position, at which the determined string starts
Length of returned string
Output CSTRING(32760) number of letters (parameter 3) of the entered text starting from the entered letternumber (parameter 2)
F_BLOBMID - counting for parameter 2 starts with 0
F_RBLOBMID - counting for parameter 2 starts with 1
TestSQL (to use with TestISO.GDB)
SELECT 'zwei' AS ISCORRECT, F_BLOBMID(TEXTBLOB, 4, 4) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT NULL AS ISCORRECT, F_BLOBMID(TEXTBLOB, NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_BLOBRIGHT compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
substitutable with F_RIGHT
substitutable with RIGHT
Entrypoint blobright
Input BLOB
INTEGER
TextBLOb
Number of indications from right
Output CSTRING(32760) string which is cut to the number of indications counted from right from parameter 2
Counting starts with 1
TestSQL (to use with TestISO.GDB)
SELECT 'dritten Zeile' AS ISCORRECT, F_BLOBRIGHT(TEXTBLOB, 13) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOBRIGHT(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_BLOBCAT  compatibility to zu GrUDF
obsolete,  substitutable with || (like strings)
Entrypoint blobcat
Input BLOB
BLOB
TextBLOb to join with TextBLOb from parameter 2
TextBLOb to append to TextBLOb from parameter 1
Output BLOB Joins two TextBLObs to one TextBLOB
This function appends an CRLF and than append the 2nd BLOb
TestSQL (to use with TestISO.GDB)
INSERT INTO BLOBTEST (TEXTBLOB) SELECT F_BLOBCAT(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 1;
Insert a new record in table BLOBTEST with the content of the field TEXTBLOB form datarow with ID = 1 append with the datarow ID = 2
nach oben • go top •
F_BLOBCATSTR  compatibility to GrUDF
obsolete,  substitutable with || (like strings)
Entrypoint blobcatstr
Input BLOB
CSTRING(32760)
TextBLOb to join with String from parameter 2
String to append to TextBLOb from parameter 1
Output BLOB Joins a TextBLObs and a string to a TextBLOB
This function appends an CRLF and than append the string.
TestSQL (to use with TestISO.GDB)
INSERT INTO BLOBTEST (TEXTBLOB) SELECT F_BLOBCATSTR(TEXTBLOB, 'Diese Zeile wurde angehängt') FROM BLOBTEST WHERE BLOBTESTID = 1;
Insert a new record in table BLOBTEST with the content of the field TEXTBLOB form datarow with ID = 1 append with tthe string ‘Diese Zeile wurde angehängt'
nach oben • go top •
F_BLOBREPLACESTRING  function from adhoc
substitutable with REPLACESTRING
Entrypoint blobreplacestring
Input BLOB
CSTRING(254)
CSTRING(254)
INTEGER
INTEGER
TextBLOb to be modified by replacing a string
the string to be replaced in BLOb
new string
0 = just replace the first appearance, 1 = replace all appearance
0 = case sensitive, 1 = not case sensitive
Output BLOB TextBLOb with replaced one/all character strings fromParameter 2 of the the BLOB by the character string from Parameter 3
TestSQL (to use with TestISO.GDB)
SELECT 'vier einzeiliger TextBLOb' AS ISCORRECT, F_BLOBREPLACESTRING(TEXTBLOB, 'ein', 'vier', 0, 0) FROM BLOBTEST WHERE BLOBTESTID = 1;
SELECT 'vier vierzeiliger TextBLOb' AS ISCORRECT, F_BLOBREPLACESTRING(TEXTBLOB, 'ein', 'vier', 1, 0) FROM BLOBTEST WHERE BLOBTESTID = 1;
nach oben • go top •
F_BLOBSUBSTR compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (B_STRPOS)
substitutable with F_SUBSTR
Entrypoint blobsubstr
Input BLOB
CSTRING(1024)
TextBLOb (in which you are looking for the string from parameter 2)
String (you are looking for in the BLOb)
Output INTEGER position in the BLOb where the string starts
Counting starts with 0.
TestSQL (to use with TestISO.GDB)
SELECT 4 AS ISCORRECT, F_BLOBSUBSTR(TEXTBLOB, 'einzeiliger') FROM BLOBTEST WHERE BLOBTESTID = 1;
SELECT NULL AS ISCORRECT, F_BLOBSUBSTR(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_BLOBLINE compatibility to FreeUDFLib, FreeUDFLib AvERP
input/output-compatibility to rFunc (B_LINE, B_LONGLINE)
Entrypoint blobline
Input BLOB
INTEGER
TextBLOb
No. of row for output
Output CSTRING(32760) the content of the <parameter 2> row of the TextBLOb
TestSQL (to use with TestISO.GDB)
SELECT 'mit einer zweiten Zeile' AS ISCORRECT, F_BLOBLINE(TEXTBLOB, 2) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOBLINE(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
 
BLOb-functions: Calculate
F_BLOBSIZE compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (B_TOTAL_LENGTH)
Entrypoint blobsize
Input BLOB TextBLOb
Output INTEGER For TextBLOb: returns the size/length (similar F_STRINGLENGTH)
For BinarBLOb: returns the size of file in byte
CRLF are countered as 2 characters
TestSQL (to use with TestISO.GDB)
For a TextBLOb:
SELECT 50 AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB)) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT 0 AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB))
FROM BLOBTEST WHERE BLOBTESTID = 11;
SELECT NULL AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB))
FROM BLOBTEST WHERE BLOBTESTID = 10;
For a BinärBLOb:
SELECT 1426 AS ISCORRECT, F_BLOBSIZE(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 4;
nach oben • go top •
F_BLOBMAXSEGMENTLENGTH compatibility to FreeUDFLib
input/output-compatibility to rFunc (B_MAX_SEGMENT)
Entrypoint blobmaxsegmentlength
Input BLOB TextBLOb or BinärBLOb
Output INTEGER Countered bytes of the largest BLOB-segment
TestSQL (to use with TestISO.GDB)
For a TextBLOb:
SELECT 16384 AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 8;
SELECT NULL AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
For a BinärBLOb:
SELECT 16384 AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 7;
nach oben • go top •
F_BLOBSEGMENTCOUNT compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (B_NUMBER_SEGMENTS)
Entrypoint blobsegmentcount
Input BLOB TextBLOb or BinärBLOb
Output INTEGER No. of  BLOb-segments
TestSQL (to use with TestISO.GDB)
For a TextBLOb:
SELECT 3 AS ISCORRECT, F_BLOBSEGMENTCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 8;
SELECT NULL AS ISCORRECT, F_BLOBSEGMENTCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
For a BinärBLOb:
SELECT 2 AS ISCORRECT, F_BLOBSEGMENTCOUNT(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 7;
nach oben • go top •
F_BLOBLINECOUNT function from adhoc
input/output-compatibility to rFunc (B_LINE_COUNT)
Entrypoint bloblinecount
Input BLOB TextBLOb
Output INTEGER No. of lines in a TextBLOb
TestSQL (to use with TestISO.GDB)
SELECT 3 AS ISCORRECT, F_BLOBLINECOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOBLINECOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
F_BLOBWORDCOUNT function from adhoc
substitutable with F_WORDCOUNT
Entrypoint blobwordcount
Input BLOB TextBLOb
Output INTEGER No. of words in a TextBLOb
A word ends with one ore more spaces, TAB or CRLF.
TESTSQL
SELECT 11 AS ISCORRECT, F_BLOBWORDCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOBWORDCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
nach oben • go top •
 
BLOb-functions: Compare
F_BLOBCOMPARE compatibility to FreeUDFLib, GrUDF (BLOBICOMP)
obsolete, BLOBs could be compared like strings
Entrypoint blobcompare
Input BLOB
BLOB
BLOB 1
BLOB 2
Output INTEGER 1 = identically, 0 = not identically
TestSQL (to use with TestISO.GDB)
SELECT 1 AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT NULL AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
SELECT 0 AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 3;
nach oben • go top •
F_BLOBCMP input/output-compatibility to rFunc (B_STRCMP)
obsolete, BLOBs could be compared like strings
Entrypoint b_strcmp
Input BLOB
BLOB
BLOb 1
BLOb 2
Output INTEGER output > 0 : BLOb 1 is smaller than BLOb 2
output = 0 : BLOb 1 = BLOb 2
output < 0 : BLOb 1 is greater than BLOb 2
Attention: in original rFunc-functions B_STRCMP the output positiv or negativ is different to the rFunc-String-function 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 (für TestISO.GDB/FDB)
SELECT 0 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT 1 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 3;
SELECT -1 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 3)) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT NULL AS ISCORRECT, F_BLOBCMP(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 3;
nach oben • go top •
 
BLOb-functions: Find / Determin
F_BLOBSTRPOS input/output-compatibility to rFunc (B_STRPOS)
substitutable with F_STRPOS
Entrypoint b_strpos
Input CSTRING(8190)
BLOB
String to search in BLOb
BLOb in which is to search
Output INTEGER starting-position of the string in BLOb, 0 if not found
case/uppercase for search-string is important. Counting for starting-position starts at 1.
TestSQL (for TestISO.GDB/FDB)
SELECT 5 AS ISCORRECT, F_BLOBSTRPOS('zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT 0 AS ISCORRECT, F_BLOBSTRPOS('Zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT NULL AS ISCORRECT, F_BLOBSTRPOS(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 2;
nach oben • go top •
F_BLOBTEXTPOS input/output-compatibility to rFunc (B_TEXTPOS)
Entrypoint b_textpos
Input CSTRING(8190)
BLOB
String to search in BLOb
BLOb in which is to search
Output INTEGER starting-position of the string in BLOb, 0 if not found
case/uppercase for search-string is not important. Counting for starting-position starts at 1.
TestSQL (for TestISO.GDB/FDB)
SELECT 5 AS ISCORRECT, F_BLOBTEXTPOS('zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT 5 AS ISCORRECT, F_BLOBTEXTPOS('Zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2;
SELECT NULL AS ISCORRECT, F_BLOBTEXTPOS(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 2;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site