Main rFunc UDF Library
version 2.1.3 RC

    The library represents a set of user's (UDF) string, bit, numerical functions, and also for operation with dates&time and BLOBs. And also PARSER, i.e. calculator of expressions. Are supported InterBase 4.2, 5.x, 6.x (Windows, Linux), Firebird 1.x (Windows, Linux, FreeBSD) and Yaffil 1.x. The library include more than 140 functions and "pseudo-functions" (it when some string UDFs have one base function, in main it is functions with the LongXXXX name). The library is written on C++ and is delivered with source codes.

The order of compilation and installation is described in the readmeeng.txt file from the distribution archive.
functions

Legend:

Symbol Type of parameter
f DOUBLE PRECISION
si SMALLINT
i INTEGER
d DATE for IB4.2 and IB5.x
TIMESTAMP for IB6 or later
c CHAR(1)
c" CHAR(32)
c* VARCHAR(176)
s VARCHAR(255)
S VARCHAR(16383)
s' VARCHAR(8)
s" VARCHAR(31)
s"' VARCHAR(38)
S** VARCHAR(8127) for IB4.2
VARCHAR(16383) for IB5.x or later
B BLOB

Function Type Versions Returns
IB* rFunc**
Numerical
Abs(f) f 4-7 1.0 The module of number
Ceil(f) f Minimum greater whole
Floor(f) f The maximum smaller whole
MaxNum(f1, f2) f Greater of numbers
MinNum(f1, f2) f Smaller of numbers
Power(f1, f2) f Number f1 in a degree f2
Round(f, i) f 4-7 1.1 The rounded off value f up to i signs, and for want of i > 0 is rounded off a fractional part, and for want of i < 0 - whole
SoftRound(f, i) f 4-7 1.5.0 Similarly Round, but if the outcome of roundoff is equal 0, the roundoff is not produced.
Z(f) f 4-7 1.3.0 If the parameter has value NULL, 0. In all other cases that is transferred comes back.
IIf(i1, i2, i3) i 4-7 1.3.0 If i1=0, i3, in other cases - i2
DIf(i, f1, f2) f 4-7 1.3.0 If i=0, f2, in other cases - f1
DZero(f1, f2, f3) f 4-7 1.3.0 Quotient f1 and f2. If f2 = 0, returns f3.
CalcExpr(S1, S2)

For example:
CalcExpr('Sin( Pi*:p11/3)*:P2', 'P11=2;P2=10') 		f 4-7 1.3.0
The unpretentious analyzer of expressions.
Supported functions and operations

+ - * /
Sin, Cos, tg, ctg, ArcCos, ArcSin, ArcTg, sh, ch, th,
exp, lh, lg, sqrt, sqr, abs(x1)
sgn(x1) - the sign of number x1
log(x1,x2) - log x1 on the basis x2
power(x1,x2) - exponentation x1 in a degree x2
if(expr, x1, x2) - if expr=0, then x2, else x1
Min(x1, ...) - minimum from numbers
Max(x1, ...) - maximum from numbers
Sum(x1, ...) - toting of arguments
Avg(x1, ...) - average arithmetic
Pi - number Pi.

     For usage of variables in expression - before them the colon is put, and they are described in the second parameter (see example).
ExprIsValid(S1, S2) i 4-7 1.3.0 Check on a correctness of the given expression. Returns 0, if all is right, differently - code of an error.
Div(i1, i2) i 4-7 1.5.0 The whole quotient i1 and i2
Mod(i1, i2) i 4-7 1.5.0 Residual from division i1 on i2
InitRandom(i) i 4-7 2.0.0 Initialization of the generator of pseudorandom numbers.
GetRandom(i) i 4-7 2.0.0 Pseudorandom numbers in a range 0..i.
IEqual(i1, i2) i 4-7 2.1.1 Equality test (1 means equality)
Sign(f) si 4-7 2.1.1 Sign of a number (-1 if f<0, 0 if f=0, 1 if f>0).
Sqrt(f) f 4-7 2.1.3 The positive square root
Exp(f) f 4-7 2.1.3 Constant e raised to the specified power
Log(f1, f2) f 4-7 2.1.3 The logarithm of a number f1 to the base f2
Log10(f) f 4-7 2.1.3 Base-10 logarithm of a number
Ln(f) f 4-7 2.1.3 The natural logarithm
E f 4-7 2.1.3 Constant E (2.7182818284590451)
Trigonometric
ACos(f) f 4-7 2.1.3 The arc cosine
ASin(f) f The arc sine
ATan(f) f The arc tangent
ATan2(f1, f2) f The arc tangent of f1/f2
Cos(f) f The cosine
CosH(f) f The hyperbolic cosine
Sin(f) f The sine
SinH(f) f The hyperbolic sine
Tan(f) f The tangent
TanH(f) f The hyperbolic tangent
Pi f Constant Pi (3.1415926535897931)
String
Chr(si) c 4-7 1.1 Character on the code
Ord(c) si 4-7 1.1 The code of character
StrReplace(s1, s2, s3) s 4-7 1.2.1 Substitutes in string s1 a substring s2 on s3
LongStrReplace(S1, S**2, S**3) S 1.2.0
StrStuff(s1, i1, i2, s2) s 4-7 1.0 In string s1 deletes i2 of characters, since i1, and inserts on this place s2
LongStrStuff(S1, i1, i2, S2) S 1.2.0
LTrim(s) s 4-7 1.0 Delete blanks at the left
LongLTrim(S) S 1.2.1
RTrim(s) s 4-7 1.0 Delete blanks on the right
LongRTrim(S) S 1.2.1
StrLen(S) i 4-7 1.0 Length of string
StrPos(s1, S2) i 4-7 1.0 Position of string s1 in string S2. If not is found, 0.
StrRepeat(s, i) s 4-7 1.0 Duplicates string s i times
LongStrRepeat(S, i) S 1.2.1
SubStr(s, i1, i2) s 4-7 1.0 Substring of string s, since character i1 and length a maxima i2.
I1 < 0 - counting happens from an extremity of string,
I2 < 0 - the characters to the left of a point of counting are output.
LongSubStr(S, i1, i2) S 1.2.1
Trim(s) s 4-7 1.0 Delete all blanks on the right and at the left
LongTrim(S) S 1.2.1
CIf(i, s1, s2) s 4-7 1.3.0 If i = 0, s2, in remaining cases - s1
LongCIf(i, S1, S2) S
StrCount(s1, S2) i 4-7 1.3.0 Amount of entrances s1 in S2
WordCount(S, s", si) i 4-7 1.3.0 Amount of words in string S, divided in characters from string s". If si <> 0, some separators the contract are considered as a word.
WordNum(S, i, s", si) s 4-7 1.3.0 i-th word in string S, divided in characters from string s". If si <> 0, some separators the contract are considered as a word.
LongWordNum(S, i, s", si) S
RLower(s) s 4-7 1.4.0 String in lowercase (WIN1251 only)
LongRLower(S) S 1.5.0
RUpper(s) s 4-7 1.4.0 String in uppercase (WIN1251 only)
LongRUpper(S) S 1.5.0
PadLeft(s, i, c) s 4-7 2.0.0 Supplements string s up to length in i character c at the left.
LongPadLeft(S, i, c) S
PadRight(s, i, c) s 4-7 2.0.0 Supplements string s up to length in i character c at the right.
LongPadRight(S, i, c) S
RLatin(s) s 4-7 2.0.0 Substitutes characters from win1251 on "similar" of a Latin.
LongRLatin(S) S
RTranslit(s) s 4-7 2.0.0 Transliteration from win1251 in a Latin.
LongRTranslit(S) S
StrCmp(S1, S2) si 4-7 2.1.1 String comparing (si<0 - first is less, si>0 - equal, si>0 - first is more)
C(s) s 4-7 2.1.1 Returns empty string, if input parameter is Null.
LongC(S) S
CEqual(S1, S2) i 4-7 2.1.1 Equality test (1 means equality)
RepeatTrim(s1, s2) s 4-7 2.1.1 Deletes symbol s2 duplication in string s1 .
Example:
RepeatTrim('1223', '2') = '123'
LongRepeatTrim(S1, S2) S
ConvertSymbols(s1, s2, s3) i 4-5 1.0 Exchanges symbols from charset s2 to charset s3 in string s1.
Example:
ConvertSymbols('1+2=3', '123', 'abc') = 'a+b=c'
LongConvertSymbols(S1, s2, s3) 4-7 2.1.1
Dates & Time
DayPerMonth(i1, i2) i 4-7 1.0 Number of days in i1 a month i2 of a year
Dow(d) i 4-7 1.0 Day of week (1 - Monday, ..., 7 - Sunday)
ExtractDate(d) d 4-7 1.2.3 Extracts date from date+time
ExtractTime(d) 2.0.0 Extracts time from date+time
Day(d) i 4-5 1.0 Day of date
ExtractDay(d) 4-7 2.1.1
Month(d) i 4-5 1.0 Month of date
ExtractMonth(d) 4-7 2.1.1
Year(d) i 4-5 1.0 Year of date
ExtractYear(d) 4-7 2.1.1
Hour(d) i 4-5 1.5.2 Hour of time
ExtractHour(d) 4-7 2.1.1
Minute(d) i 4-5 1.5.2 Minute of time
ExtractMinute(d) 4-7 2.1.1
Second(d) i 4-5 1.5.2 Second of time
ExtractSecond(d) 4-7 2.1.1
ExtractMillisecond(d) i 6-7 2.1.3 Millisecond of time
WeekDay(d) i 4-5 2.0.0 Day of week. (0 - Sunday, 1 - Monday, ..., 6 - Saturday)
ExtractWeekDay(d) 4-7 2.1.1
YearDay(d) i 4-5 2.0.0 Day of year
ExtractYearDay(d) 4-7 2.1.1
Quarter(d) i 4-7 2.0.0 Quarter of a year
FirstDayMonth(d) d 4-7 2.0.0 The first day of a month
LastDayMonth(d) d 4-7 2.0.0 The last day of a month
IsLeapYear(i) i 4-7 2.0.0 1 - leap-year, 0 - unleap
DaysBetween(d1, d2) i 4-7 2.0.0 Amount of days between dates
DtIf(i, d1, d2) d 4-7 2.0.0 If i = 0, d2, in other cases - d1
IncDate(d, i1, i2, i3) d 4-7 2.0.1 Change of date on i1 days, i2 months, i3 years.
IncDateTime(d, i1, i2, i3, i4, i5, i6) d 4-7 2.1.1 Change of date on i1 days, i2 months, i3 years, i4 hours, i5 minutes, i6 seconds.
MaxDate(d1, d2) d 4-7 2.1.1 Greater of dates
MinDate(d1, d2) d 4-7 2.1.1 Smaller of dates
DtEqual(d1, d2) i 4-7 2.1.1 Equality test (1 means equality)
Datatype conversions
DateToStr(d, s)

For example: DateToStr('10.02.99','%d.%m. %y') = '10.02.99'
DateToStr('10.02.04 08:23','%d %B %Y, %X') =
'10 February 2004, 08:23:00' 		s 4-7 1.0
Conversion of date d in string using a format s.
Formatting strings
(correspond with c-function strftime):
%% - character %,
%c - date and time,
%x - date,
%d - day of a month (01-31),
%j - day of year (000-366),
%w - day of week (0-6), 0 - Sunday,
%U - number of week in one year (00-53), Sunday - the first day of week,
%W - number of week in one year (00-53), Monday - the first day of week,
%a - the short name of day of week,
%A - the full name of day of week,
%m - month (01-12),
%b - the short name of day of month,
%B - the full name of day of month,
%y - year (00-99),    %Y - four-digit year,
%X - time,
%H - hour (00-23),   %I - hour (01-12),
%M - minute (00-59),
%p - AM or PM,
%S - second (00-59).
EncodeDate(i1, i2, i3) d 4-7 1.0 Date on format i1.i2.i3 = dd.mm.yyyy
EncodeDateTime(i1, i2, i3, i4, i5, i6) d 4-7 1.5.2 Date and time on format
i1.i2.i3 i4:i5:i6 = dd.mm.yyyy hh:nn:ss
DoubleToDate(f) d 4-7 2.1.2 Converts double to date.
DoubleToTime(f) d 4-7 2.1.2 Converts double to time.
DateToDouble(d) f 4-7 2.1.1 Converts date to double.
TimeToDouble(d) f 4-7 2.1.1 Converts time to double.
FloatToStr(f, s)

For example:
FloatToStr(13.5, '%7.3e') = '1.350e+01'
FloatToStr(13.5, '%+7.3f') = ' +13.500'
FloatToStr(13.5345, '%.5g') = '13.534'
FloatToStr(13.5345, '%.5f') = '13.53450'
FloatToStr(13, '%05X') = '0000D'
FloatToStr(13, '%#04x') = '0x0d'

s 4-7 2.1.1
Conversion of double f to string using a format s.
Formatting strings
(as in c-function sprintf):
% [flags] [width] [.prec] type_char
flags:
- left justify
+ begin with plus or minus sign
blank blank instead of a plus
# alternate conversion:
0x (0X) before for x,X type_char
always a decimal point for e,E,f
width:
n at least n chars, padded by blanks
0n at least n chars, padded by zeros
prec:
(none) 1 for d,i,o,u,x,X types
6 for e,E,f types
all significant digits for g,G
.0 for d,i,o,u,x types, precision set to default
for e,E,f types, no decimal point
.n for d,i,o,u,x types, at least n digits
for e,E,f types, n digits after the decimal point
for g,G, at most n significant digits
type_char:
d,i signed decimal integer
o unsigned octal integer
u unsigned decimal integer
x,X unsigned hexadecimal int
f signed value of the form [-]dddd.dddd
e signed value of the form [-]d.dddd or e[+/-]ddd
g signed value in either e or f form, based on given value and precision
E,G same as e,g, but with E for exponent
IntToStr(i, s) s 4-7 2.1.1 Conversion of integer i to string using a format s (See FloatToStr)
NumInWords(i, с) s 4-7 2.1.3 Number in words (WIN1251 only). с is grammatical gender
BLOB
B_Line(B, i) s 4-7 2.0.0 i-th line of B
B_LongLine(B, i) S
B_Line_Count(B) i Amount of lines
B_Max_Segment(B) i Maximum length of a segment
B_Number_Segments(B) i Amount of segments
B_Put_Segment(S) B Put a segment in a blob-field (example see in rtest.sql)
B_SubStr(B, i1, i2) s Similar SubStr
B_LongSubStr(B, i1, i2) S
B_Total_Length(B) i Amount of bytes in B
B_StrCmp(B1, B2) si 4-7 2.1.2 Comparing of two string BLOBs. Similar StrCmp
B_StrPos(S, B) si 4-7 2.1.2 Search substring in BLOB. Similar StrPos
B_TextPos(S, B) si 4-7 2.1.2 Similar B_StrPos, but not case sensitive
Bit
BitAnd(i1, i2) i 4-7 2.0.0 i1 and i2
BitNot(i) i not(i)
BitOr(i1, i2) i i1 or i2
BitXor(i1, i2) i i1 xor i2
GetBit(i1, i2) i i2-th bit of number i1
SetBit(i1, i2, i3) i Substitutes i2-th bit of number i1 on i3. i3 can accept values 0 and 1.
API

Add_User(i, s1, s"2, s'3, s"4, s"5, s"6, s"7, s"8, s'9)

For example:
Add_User(1, 'ibserver', 'uname', 'password', '', 'john', 'jr', 'Doe', 'sysdba', 'masterke')

i 5-7 1.3.0 Addition and change of properties of the user. API InterBase. Adds (changes) the user with the s"2 name on the s1 server from UNIX-group s"4. The connection is produced under protocol i (1-TCPIP, 2-NetBEUI, 3-SPX, 4-locally) user s"8 with the password s'9. Parameters: s'3 - password, s"5 - name, s"6 - patronymic, s"7 - surname. Returns an exit code (0-normal).
Modify_User(i, s1, s"2, s'3, s"4, s"5, s"6, s"7, s"8, s'9) i
Delete_User(i, s1, s"2, s"8, s'9) i 5-7 1.3.0 Deleting of the user. API IB InterBase. Deletes the user with the s"2 name on the s1 server. The connection is produced under protocol i (1-TCPIP, 2-NetBEUI, 3-SPX, 4-locally) user s"8 with the password s'9.
MsgBox(S1, s2, i) i 5-7
Windows
Only 		1.3.0

The window of the message with possibility of a choice. It is not recommended to use on remote servers. API Windows. Outputs the window with header s2 and text S1.
Special
LibVersion s 4-7 2.1.1 The rFunc library version
LibName s 4-7 2.1.1 The library name (always "rfunc")
EAN13cs(s) si 4-7 2.1.1 Calculation of EAN13's thirteenth digit.
MD5Sum(S) c" 4-7 2.1.3 Сhecksum by MD5 algorithm
MD5Init c* 4-7 2.1.3 Block-to-block calculation of checksum by MD5 algorithm.
Usage:
- declare a variable context CHAR(176) for temporary result;
- declare a variable md5 CHAR(32) for final result;
- execute context = md5Init();
- in loop (or as needed) execute context = md5Update(context, S, SLen), where are
context - understandably,
S - regular input string,
SLen - processing length;
- execute md5 = md5Final(context);
MD5Update(c*, S, i) c*
MD5Final(c*) c"
CreateGUID s"' 4-7 2.1.3 GUID as string
File
This functions is optional. They are not compiled without setting special option (see readmeeng.txt). Use this functions set very carefully. Applying of this functions may damage or delete files on your server disk.
FCreate(s, i) i 4-7 2.1.2 Create file with name s. Create mode is i (combination of FS_xxx). Returns file handle or a negative number if error occured.
FOpen(s, i1, i2) i Open file with name s. Access flags are specified by i1. i2 is mode for create file (i1=FO_CREAT). Returns file handle or a negative number if error occured.
FRead(i1, i2) s Reads i2 bytes from file with handle i1.
LongFRead(i1, i2)
FWrite(i, S) i Writes bytes from string S into file with handle i. Returns number of written bytes.
FSeek(i1, i2, i3) i Sets the file pointer associated with i1 to a new position that is i2 bytes from the file location given by i3.
FClose(i) i Close file with handle i. Returns 0.
FRemove(s) i 2.1.3 Deletes file with name s.
FSize(s) i 2.1.3 Size of file
Functions-constants for i1 parameter of FOpen:
FO_RDONLY
FO_WRONLY
FO_RDWR
FO_CREAT
FO_TRUNC
FO_EXCL		     2.1.2 Open for reading only.
Open for writing only.
Open for reading and writing.
If the file does not exist, the file is created.
If the file exists, its length is truncated to 0.
Exclusive open. Used only with FO_CREAT.
FO_APPEND 2.1.3 File pointer will be set to the end of the file prior to each write.

Functions-constants for i3 parameter of FSeek:
FSEEK_SET
FSEEK_CUR
FSEEK_END

 i 4-7 2.1.2 File beginning.
Current file pointer position.
End-of-file.
Functions-constants for i3 parameter of FOpen and i2 of FCreate:
FS_IREAD
FS_IWRITE
FS_IEXEC		 i 4-7 2.1.3 Owner can read
Owner can write
Owner can execute

* - Firebird 1.x and Yaffil 1.x are equals Interbase 6
** - First appearance of a function

changes history

Please, on all questions and remarks connected with rFunc UDF Library, access to rFunc@mail.ru or forums.


Copyright © 2003 Polaris Software