![]() |
![]() ![]() ![]() ![]() ![]() ![]() |
|
![]() ![]() ![]() ![]() ![]() |
![]() ![]() ![]() ![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Numeric-functions | 72 functions | |
Create | 4 functions | |
Format | 11 functions | |
Calculate | 20 functions | |
Compare | 4 functions | |
Binary functions | 8 functions | |
Trigonometry | 25 functions | |
![]() |
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. |
Numeric-functions: Format | ||
![]() |
||
F_ROUND | input/output-compatibility to FreeUDFLibC | |
Entrypoint | f_round | |
Input | DOUBLE | floatingpoint to be round as an integer |
Output | INTEGER | integer |
The method of rounding is the common method
like F_ZAHLRUNDEN. Because there is a same-named function in C we must change the entrypoint from round to f_round. Therefore now NO entryppoint-compatibility to FreeUDFLibC. TestSQL SELECT 16 AS ISCORRECT, F_ROUND(15.567) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ROUND(NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_ROUNDFLOAT | compatibility FreeUDFLib, FreeUDFLib AvERP | |
Entrypoint | roundfloat | |
Input | DOUBLE DOUBLE |
floatingpoint
to be round output pattern for floatingpoint (e.g. 0.01) rounds on the next multiple of parameter 2 |
Output | DOUBLE | floatingpoint |
TestSQL SELECT 15.55 AS ISCORRECT, F_ROUNDFLOAT(15.567, 0.05) FROM RDB$DATABASE; SELECT 15.56 AS ISCORRECT, F_ROUNDFLOAT(15.567, 0.02) FROM RDB$DATABASE; SELECT 15.57 AS ISCORRECT, F_ROUNDFLOAT(15.567, 0.01) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ROUNDFLOAT(NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_ZAHLRUNDEN F_ROUNDCOMMON |
compatibility
FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (ROUND) |
|
![]() |
substitutable with ROUND | |
Entrypoint | zahlrunden | |
Input | DOUBLE INTEGER |
floatingpoint
to be round number of digits to be round |
Output | DOUBLE | floatingpoint rounded (trading method) |
Common
Round (from Wikipedia
http://en.wikipedia.org/wiki/Rounding): This method is commonly used, for example in accounting. It is the one generally taught in basic mathematics classes. * Decide which is the last digit to keep. * Increase it by 1 if the next digit is 5 or more (this is called rounding up) * Leave it the same if the next digit is 4 or less (this is called rounding down) Examples: * 3.044 rounded to hundredths is 3.04 (because the next digit, 4, is less than 5). * 3.046 rounded to hundredths is 3.05 (because the next digit, 6, is 5 or more). * 3.0447 rounded to hundredths is 3.04 (because the next digit, 4, is less than 5). Negativ numbers are rounded like their absolute values: * −2,1349 to −2,13 € * −2,1350 to −2,14 € TestSQL SELECT 14.5 AS ISCORRECT, F_ZAHLRUNDEN(14.4935, 1) FROM RDB$DATABASE; SELECT 14.49 AS ISCORRECT, F_ZAHLRUNDEN(14.4935, 2) FROM RDB$DATABASE; SELECT 14.494 AS ISCORRECT, F_ZAHLRUNDEN(14.4935, 3) FROM RDB$DATABASE; SELECT 14.4935 AS ISCORRECT, F_ZAHLRUNDEN(14.4935, 6) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ZAHLRUNDEN(NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_ROUNDTOEVEN | function from adhoc | |
Entrypoint | roundtoeven | |
Input | DOUBLE INTEGER |
floatingpoint
to be round number of digits to be round |
Output | DOUBLE | floatingpoint rounded (round to even method) |
Round to even (from
Wikipedia
http://en.wikipedia.org/wiki/Rounding): This method is also known as statistician's rounding or as bankers' rounding. It is identical to the common method of rounding except when the digit(s) following the rounding digit start with a five and have no non-zero digits after it. The new algorithm is: * Decide which is the last digit to keep. * Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits. * Leave it the same if the next digit is 4 or less * Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes; then change the last digit to the nearest even digit. That is, increase the rounded digit if it is currently odd; leave it if it is already even. With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or leaving it alone. With traditional rounding, if the number has a value less than the half-way mark between the possible outcomes, it is rounded down; if the number has a value exactly half-way or greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the same except that numbers exactly half-way between the possible outcomes are sometimes rounded up—sometimes down. Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4 (it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends are important, traditional rounding on average biases the data upwards slightly. Over a large set of data, or when many subsequent rounding operations are performed as in digital signal processing, the round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers rounding up as rounding down. This generally reduces the upwards skewing of the result. Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero. Examples: * 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more) * 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less) * 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd) * 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even) * 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits) Negativ numbers are rounded like their absolute values: * −2,35 rounded to tenths is −2,4 * −2,45 (exact) rounded to tenths is −2,4 * −2,4500001 rounded to tenths is −2,5 * −2,46 rounded to tenths is −2,5 * −2,449 rounded to tenths is −2,5 TestSQL SELECT 3.02 AS ISCORRECT, F_ROUNDTOEVEN(3.016, 2) FROM RDB$DATABASE; SELECT 3.01 AS ISCORRECT, F_ROUNDTOEVEN(3.013, 2) FROM RDB$DATABASE; SELECT 3.02 AS ISCORRECT, F_ROUNDTOEVEN(3.015, 2) FROM RDB$DATABASE; SELECT 3.04 AS ISCORRECT, F_ROUNDTOEVEN(3.045, 2) FROM RDB$DATABASE; SELECT 3.05 AS ISCORRECT, F_ROUNDTOEVEN(3.04501, 2) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_ROUNDTOEVEN(NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_SOFTROUND | input/output-compatibility to rFunc (SOFTROUND) | |
Entrypoint | softround | |
Input | DOUBLE INTEGER |
floatingpoint
to be round number of digits to be round |
Output | INTEGER | floatingpoint rounded ti integer |
The method of rounding is the common method
like F_ZAHLRUNDEN. If the result of rounding would be 0, than the result is the non-rounded value. TestSQL SELECT 0.0016 AS ISCORRECT, F_SOFTROUND(0.0016, 2), F_ZAHLRUNDEN(0.0016, 2) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_SOFTROUND(NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_FIXEDPOINT | compatibility FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP | |
Entrypoint | fixedpoint | |
Input | DOUBLE INTEGER |
floatingpoint
to be round number of digits to be round |
Output | CSTRING(32) | floatingpoint as a string |
The
method of rounding is round to even like F_ROUNDTOEVEN. In difference to F_ZAHLRUNDEN the output is a String! In string-expenditure the decimal-separator is a "." TestSQL SELECT '12345.5' AS ISCORRECT, F_FIXEDPOINT(12345.46, 1) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FIXEDPOINT(NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_FIXEDPOINTLANG | function
from adhoc |
|
Entrypoint | fixedpointlang | |
Input | DOUBLE INTEGER CSTRING(1) CSTRING(1) |
floatingpoint
to be round number of digits to be round Indicator of decimal-separation Indicator of thousands seperator |
Output | CSTRING(32) | floatingpoint as a string with defined decimal- and thousands separation |
The method of rounding is the common method
like F_ZAHLRUNDEN. TestSQL SELECT '12.345,5' AS ISCORRECT, F_FIXEDPOINTLANG(12345.46, 1, ',', '.') FROM RDB$DATABASE; SELECT '12.345' AS ISCORRECT, F_FIXEDPOINTLANG(12345.46, 0, ',', '.') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_FIXEDPOINTLANG(NULL, NULL, NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_TRUNCATE | compatibility FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF | |
![]() |
substitutable with TRUNC | |
Entrypoint | f_truncate | |
Input | DOUBLE | floatingpoint to be truncat |
Output | INTEGER | integer |
TestSQL SELECT 15 AS ISCORRECT, F_TRUNCATE(15.567) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_TRUNCATE(NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_DOLLARVAL F_CONVERTTODOLLAR |
compatibility
FreeUDFLib,
FreeUDFLib AvERP compatibility FreeUDFLibC |
|
Entrypoint | dollarval | |
Input | DOUBLE | floatingpoint |
Output | CSTRING(32) | Dollar-floatingpoint ($ in front of the number) (rounded to 2 digits) |
TestSQL SELECT '$15.68' AS ISCORRECT, F_CONVERTTODOLLAR(15.678) FROM RDB$DATABASE; SELECT '$15.68' AS ISCORRECT, F_DOLLARVAL(15.678) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_DOLLARVAL(NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_EUROVAL | function
from adhoc |
|
Entrypoint | euroval | |
Input | DOUBLE | floatingpoint |
Output | CSTRING(32) | EURO-floatingpoint (EUR after the number) (rounded to 2 digits) |
TestSQL SELECT '15.47 EUR' AS ISCORRECT, F_EUROVAL(15.47) FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_EUROVAL(NULL) FROM RDB$DATABASE; |
||
![]() ![]() |
||
F_NUMINWORDS | function
from adhoc |
|
Entrypoint | numinwords | |
Input | DOUBLE | floatingpoint |
SMALLINT | number of floatingpoint-digits (no rounding) | |
CSTRING(2) | language identifier for the output | |
Output | CSTRING(32) | floatingpoint in words in chosen language |
Language
identifier: de = German, uk = English, fr = French, es =
Spanish, it =Italian, es = Spanish, pt = Portuguese, nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic), ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots, s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese, lu = Luxembourgish, w2 = Wallon TestSQL SELECT '***eins-vier-fünf***' AS ISCORRECT, F_NUMINWORDS(145, 0, 'de') FROM RDB$DATABASE; SELECT '***eins-vier-fünf-Komma-drei-zwei***' AS ISCORRECT, F_NUMINWORDS(145.32, 2, 'de') FROM RDB$DATABASE; SELECT '***eins-vier-fünf-Komma-drei-zwei-null-null***' AS ISCORRECT, F_NUMINWORDS(145.32, 4, 'de') FROM RDB$DATABASE; SELECT '***one-four-five-point-three-two***' AS ISCORRECT, F_NUMINWORDS(145.32, 2, 'uk') FROM RDB$DATABASE; SELECT '***un-quatre-cinq-virgule-trois-deux***' AS ISCORRECT, F_NUMINWORDS(145.32, 2, 'fr') FROM RDB$DATABASE; SELECT '***uno-cuatro-cinco-coma-tres-dos***' AS ISCORRECT, F_NUMINWORDS(145.32, 2, 'es') FROM RDB$DATABASE; SELECT '***uno-quattro-cinque-virgola-tre-due***' AS ISCORRECT, F_NUMINWORDS(145.32, 2, 'it') FROM RDB$DATABASE; SELECT NULL AS ISCORRECT, F_NUMINWORDS(NULL, NULL, NULL) FROM RDB$DATABASE; |
||
![]() ![]() |